First, think about using FTS2 for your text lookup instead of using LIKE
and having to perform row scans.
Think about changing your logic to just read from the database instead
of blowing out your memory by holding the database on disk, then in disk
cache and again in application memory. If you want to show a table of
closely related entries, use the LIMIT feature on SELECT.
Colin Manning 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.
So I might have:
CREATE TABLE pb (id INT, name VARCHAR(40),...);
CREATE INDEX pb_idx_name ON pb(name);
..
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.
Next, assume the user wants to jump to a specific record in the list, or
to (say) the first entry for a specific name.
How do you do this with sqlite, without forcing the the app to fetch
every record and then do a manual comparison in the app?
E.g. my user wants the app's window to 'jump' to the first record with
name 'John Smith'. I.e what my windowed app wants to do is to find the
'position' x of the first record in the name index that matches 'John
Smith', then use that x to both (i) set its vertical scrollbar position,
and (ii) to then issue something like "SELECT * FROM pb ORDER BY name
LIMIT x,20", whilst retaining the illusion that the window contains the
whole phone directory.
I do not want to do "SELECT * FROM pb WHERE name LIKE 'John Smith' ORDER
BY name" because that will just return the subset of matching records.
It seems to me that I need an interface to sqlite that says 'given a
specific index, and a tuple of values for the columns upon which that
index is constructed, how many records appear before and after the index
position given by that tuple'
Any ideas, or am I missing something obvious? Sorry if this is a dumb
question.
Thanks
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
