how to get the result in the middle of resultset? - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

how to get the result in the middle of resultset?

how to get the result in the middle of resultset?# Database - 数据库

S*s2001-07-12 07:07

1 楼

almost every search engine can return the result in the middle of the whole
resultset. say one query returns total 10 k rows. while the use can choose to
have a look only to the rows of number 5000 to 5010.
how to implement this in the databse? is it a must to generate all the top
5010 rows first?

a*p2001-07-12 07:07

2 楼

In some top-level API, like in PHP, you can specify which
line to return when you call function like
mysql_fetch_array
or
odbc_fetch_row
check API documentation. I specified row number in ODBC and mySQL before but
just no source code on hand right now.
but when you select, all the 10k rows should already be in the server's cache.
this method just saves the time to transmit, if say, your mySQL server is remote.

【在 S*******s 的大作中提到】

: almost every search engine can return the result in the middle of the whole
: resultset. say one query returns total 10 k rows. while the use can choose to
: have a look only to the rows of number 5000 to 5010.
: how to implement this in the databse? is it a must to generate all the top
: 5010 rows first?

a*i2001-07-12 07:07

3 楼

Well, that would be EXTREMELY slow if you have to be able to grab all
returnsets first.
For easier implementation; you can use Limit $start, $page functionality.
select * from test_table where test_id > 10 limit 10, 10
will give you 10 results starting from the 10th one.

【在 a*p 的大作中提到】

: In some top-level API, like in PHP, you can specify which
: line to return when you call function like
: mysql_fetch_array
: or
: odbc_fetch_row
: check API documentation. I specified row number in ODBC and mySQL before but
: just no source code on hand right now.
: but when you select, all the 10k rows should already be in the server's cache.
: this method just saves the time to transmit, if say, your mySQL server is remote.

a*p2001-07-12 07:07

4 楼

I don't think the original author doesn't know this solution.
his problem is probably far more complicated.
basically, if you want to query a search engine using sth like
where str like '%you_want%'
and you want the output to be paged.
how can you generate the second/third/etc... pages?

【在 a*****i 的大作中提到】

: Well, that would be EXTREMELY slow if you have to be able to grab all
: returnsets first.
: For easier implementation; you can use Limit $start, $page functionality.
: select * from test_table where test_id > 10 limit 10, 10
: will give you 10 results starting from the 10th one.

a*i2001-07-12 07:07

5 楼

Not sure what you meant, but you can keep track of the pages by specifying
the parameter like dummypage.php?start=0&page=15&total=100.(I have done this,
I know.)
And searching engine is a different story. I think they have used c code
to optimize the middle layer between web and the db.

【在 a*p 的大作中提到】

: I don't think the original author doesn't know this solution.
: his problem is probably far more complicated.
: basically, if you want to query a search engine using sth like
: where str like '%you_want%'
: and you want the output to be paged.
: how can you generate the second/third/etc... pages?

S*s2001-07-12 07:07

6 楼

i think I mentioned "search engine" in my original post.
it is not a problem how the middle layer deal with the
parameter of start and end. the prolem is just as the title.
I don't know the limit function (so atp, what you assumed is
wrong ;). and i still don't understand how it can be used. I
can't find the description of this function from online
document.
the solution I can figure out is with cursor, only those
rows falls into that range was returned, which eleminate the
unnecessary traffice

【在 a*****i 的大作中提到】

: Not sure what you meant, but you can keep track of the pages by specifying
: the parameter like dummypage.php?start=0&page=15&total=100.(I have done this,
: I know.)
: And searching engine is a different story. I think they have used c code
: to optimize the middle layer between web and the db.

a*p2001-07-12 07:07

7 楼

definitely search engine should use more effective code like
C. but probably this is common problem in implementing
search engine.
Well, what I mean is as the following. here in this bbs, you
want to query posts whose titles contain the letter "a" and
definitely you will get lots of results, say, 1000 records
and you defintely don't want all results to be outputted in
one page, so you page it. say, if you want 50 records per
page
It's fairly easy to generate the first page, just using
"limit 50"

【在 S*******s 的大作中提到】

: i think I mentioned "search engine" in my original post.
: it is not a problem how the middle layer deal with the
: parameter of start and end. the prolem is just as the title.
: I don't know the limit function (so atp, what you assumed is
: wrong ;). and i still don't understand how it can be used. I
: can't find the description of this function from online
: document.
: the solution I can figure out is with cursor, only those
: rows falls into that range was returned, which eleminate the
: unnecessary traffice

S*s2001-07-12 07:07

8 楼

1. u both mean that search engine would rather do its own
query algorism than request against a RDBMS ? I strongly
doubt.
if it is not what you meant, I don't see too much difference
of the coding in client side, no matter it is C or not.
2. and do you know if there is the counterpart in MS SQL
Server of the "limit" function you mentioned? is this
function part of ANSI?
3. i think such function helps to those query that can be do
by a single select statment. but to those more complicated
query I

【在 a*p 的大作中提到】

: definitely search engine should use more effective code like
: C. but probably this is common problem in implementing
: search engine.
: Well, what I mean is as the following. here in this bbs, you
: want to query posts whose titles contain the letter "a" and
: definitely you will get lots of results, say, 1000 records
: and you defintely don't want all results to be outputted in
: one page, so you page it. say, if you want 50 records per
: page
: It's fairly easy to generate the first page, just using

S*s2001-07-12 07:07

9 楼

now i got a solution:
temp table. we can do the index in the temp table. then only
return those in the range back to client. according to the
DBA, such operation runs very fast.
so is the solution to my other question. insert the rows
created by cursor into the temp table. return this table at
last.
so simple, hmm?

【在 S*******s 的大作中提到】

: 1. u both mean that search engine would rather do its own
: query algorism than request against a RDBMS ? I strongly
: doubt.
: if it is not what you meant, I don't see too much difference
: of the coding in client side, no matter it is C or not.
: 2. and do you know if there is the counterpart in MS SQL
: Server of the "limit" function you mentioned? is this
: function part of ANSI?
: 3. i think such function helps to those query that can be do
: by a single select statment. but to those more complicated