Colin Manning <[EMAIL PROTECTED]> wrote: > Hi > > Assume I have a simple table for a phone directory - names, addresses, and > phone numbers etc. > > Then assume this is a vast table with many millions of records, and that > there are indices on name, phone number, etc. > > Then assume I'm writing an app that displays the entire directory in a > window, ordered by name/number etc using a scrollbar to move up and down. > [...] > > Not a problem. I can use "SELECT * FROM pb ORDER BY name LIMIT x,y" etc as > my user pages up and down the list, or drags a scrollbar.
This is not a good way of solving the problem. The OFFSET clause works by running the query from the beginning and discarding the first x records. So when you get down toward the bottom of your millions-long table, the SELECTs are going to start getting really slow. A better approach is to remember the name and rowid of the record at the top and bottom of your display window. Call these values name_top, rowid_top, name_btm, and rowid_btm. To scroll down to subsequent entries, do this: SELECT rowid, * FROM pb WHERE name>=$name_btm ORDER BY name ASC. If the same name occurs multiple times, you might get some duplicates at the beginning of your query. Use the rowid to resolve duplicates. Run the query until you have as many records as you need to fill your screen then cancel the query using sqlite3_reset(). To scroll up: SELECT rowid, * FROM pb WHERE name<=$name_top ORDER BY name DESC. Once again, you may get duplicates at the beginning of the query, which you should skip over. > > Next, assume the user wants to jump to a specific record in the list, or to > (say) the first entry for a specific name. > Just set name_btm to the name you are looking for and set rowid_btm to 0. Then run the first query. -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
