Hi,
I think this has been discussed before, but I can't find a good solution so
I'll post it again to see what people think.
Here's the problem. I have a large number of records in a table, which
contains many columns. Hence a large amount of data.
I have a SQL query that filters the results by search criteria across many
of these columns.
I am using a web front end to display paged results. I need to tell the
user how many records there are in total, how many pages and which page they
are viewing.
I would like to use the LIMIT keyword to restrict the result using the two
parameters (offset and limit count) so that I do not waste resources loading
up 1000's of records just to discard the ones not on the current page.
However if LIMIT is added to the SQL, I do not get a count of the records
that the SQL select would have produced if I had not limited the query with
LIMIT.
I could do two selects with the same WHERE restriction, one with SELECT
****(*) and the other with SELECT field1, field2, etc but this seems
wasteful, especially if the query is expensive in resources.
Perhaps a new function could be added to SQLite that returns the record
count regardless of any LIMIT applied?
e.g.
SELECT field1, field2, field3, fieldN, count_rows()
FRM table1
WHERE field99 LIKE 'G%'
AND field66 = 7
R field18 <= 5.7
LIMIT 341, 10
will return a maximum of 10 records but the count_rows() will return how
many rows the query would return if the LIMIT was not in place?
This would make paging of results very straight foreward and efficient.
Anyone agree/disagree this would be useful?

* Mikey C <spam.bucket (AT) ntlworld (DOT) com[2006-05-29 17:10]:
Do you imagine Google loads 8 billions records into memory when
the user is just viewing 10 results in page 5 after a broad
search?

You t ask Google for more than the first 1,000 hits on any
search. (Go ahead and try.) There is a reason for that. Likewise
the number Google presents for the total is just an estimate.
There is a reason for that too.

At the same time, to my knowledge, a query which has an `RDER
BY` clause always has to produce all results before it can apply
a `LIMIT` to the result set, so at least in that case, what you
want should be possible.

Regards,

"A. Pagaltzis" <pagaltzis (AT) gmx (DOT) dewrote:

At the same time, to my knowledge, a query which has an `RDER
BY` clause always has to produce all results before it can apply
a `LIMIT` to the result set, so at least in that case, what you
want should be possible.

Depends. SQLite will use an index to satisfy the RDER BY
clause if it can, and in that case it is *not* necessary to produce
all results before applying the LIMIT.

I don't think you really understand what I'm trying to say.

Web based systems require paging that does not iterate through all records.

What is required is a means to LIMIT the results read from the database but
at the same time know how many records WULD have been returned if the query
was not LIMITed.

In this way it is then possible to display results to the user:

Displaying Page 15 of 25788 pages.

Record 1
Record 2
Record
Record 10

Previous Page Link | Next Page Link

LIMIT gives this great power (ie. I only want to read 10 record starting
from record 151) BUT I do need to know how many records there are in total
for this query in order to page correctly and display how many pages there
are to the user.

I'd rather SQLite reads on 10 records and not 100 million records into
memory, just to discard them all except the 10 the user needs for that page
of results?

Does this make sense? Do you imagine Google loads 8 billions records into
memory when the user is just viewing 10 results in page 5 after a broad
search?

To perform a count one has to read the entire dataset regardless. Why
not implement your logic within your own program, using prepare and step
and ceasing to unload data from the columns when you hit your predefined
limit?
JS
Mikey C wrote:
Hi,

I think this has been discussed before, but I can't find a good solution so
I'll post it again to see what people think.

Here's the problem. I have a large number of records in a table, which
contains many columns. Hence a large amount of data.

I have a SQL query that filters the results by search criteria across many
of these columns.

I am using a web front end to display paged results. I need to tell the
user how many records there are in total, how many pages and which page they
are viewing.

I would like to use the LIMIT keyword to restrict the result using the two
parameters (offset and limit count) so that I do not waste resources loading
up 1000's of records just to discard the ones not on the current page.

However if LIMIT is added to the SQL, I do not get a count of the records
that the SQL select would have produced if I had not limited the query with
LIMIT.

I could do two selects with the same WHERE restriction, one with SELECT
****(*) and the other with SELECT field1, field2, etc but this seems
wasteful, especially if the query is expensive in resources.

Perhaps a new function could be added to SQLite that returns the record
count regardless of any LIMIT applied?

e.g.

SELECT field1, field2, field3, fieldN, count_rows()
FRM table1
WHERE field99 LIKE 'G%'
AND field66 = 7
R field18 <= 5.7
LIMIT 341, 10

will return a maximum of 10 records but the count_rows() will return how
many rows the query would return if the LIMIT was not in place?

This would make paging of results very straight foreward and efficient.

Anyone agree/disagree this would be useful?

This problem is as old as data processing. The "page N of M" situation
which requires that the entire data set be read to get M. you have
read the entire set you have many options on how you handle your window
on that dataset. You could use local storage for it all or take
advantage of a suitable B-Tree key to step define the current window and
the last key in the window to define the next window. The second method
uses no additional local storage.
JS

Mikey C wrote:
I don't think you really understand what I'm trying to say.

Web based systems require paging that does not iterate through all records.

What is required is a means to LIMIT the results read from the database but
at the same time know how many records WULD have been returned if the query
was not LIMITed.

In this way it is then possible to display results to the user:

Displaying Page 15 of 25788 pages.

Record 1
Record 2
Record
Record 10

Previous Page Link | Next Page Link

LIMIT gives this great power (ie. I only want to read 10 record starting
from record 151) BUT I do need to know how many records there are in total
for this query in order to page correctly and display how many pages there
are to the user.

I'd rather SQLite reads on 10 records and not 100 million records into
memory, just to discard them all except the 10 the user needs for that page
of results?

Does this make sense? Do you imagine Google loads 8 billions records into
memory when the user is just viewing 10 results in page 5 after a broad
search?

Igor Tandetnik wrote:

By the way, for most queries you will find that the execution time
grows as the FFSET grows. To implement FFSET X, the engine would
enumerate records from the beginning and simply ignore the first X-1.
Retrieving the last pageful often takes almost as long as retrieving
all the records.

I have noticed exactly that behaviour recently in MySQL and MSSQL (where
it had to be emulated). This was when implementing a web paging access
as the original poster is doing.

5/29/06, Mikey C <spam.bucket (AT) ntlworld (DOT) comwrote:

I am using a web front end to display paged results. I need to tell the
user how many records there are in total, how many pages and which page they
are viewing.

I would like to use the LIMIT keyword to restrict the result using the two
parameters (offset and limit count) so that I do not waste resources loading
up 1000's of records just to discard the ones not on the current page.

If you can eliminate the requirement of giving the user a scroll bar or a count
of results then the need to get a count goes away.

If you can do it you're much better off forcing the user to accept
only the first N
results of any search. Nobody uses more than 10 pages or so of any interactive
result set and most people don't use more than the first couple of
pages. It really
speeds things up and simplifies things when you don't have to deal with very
large results. The users won't look at them anyway. The last time I had to do
this I had to go through a lot of trouble to get it to work acceptably fast.
I wrote special code to return the first page then do all the indexing for the
remaining pages while the user was looking at the first page.