On May 30, 2006, at 1:36 AM, Noel Frankinet wrote:
Hello All,
since nobody has answered my last question, I'll try to rephrase it :
How do you select record you have the keys (and I mean a lot of keys)
I know
SELECT .. WHERE rowid=
SELECT .. WHERE rowid IN (something like that)
SELECT ... WHARE rowid BETWEEN ...
Is there another way, does a temporary table helps ?
Using a temporary table seems to be the best way when you have lots
of keys. Of course there is a tradeoff point, such as for 1 or 2
rows a temporary table is overkill. Also it depends on if you're
temporary table is being stored in memory or on the disk, check the
relevant PRAGMA documentation to specify how you want them stored.
CREATE TEMP TABLE search_keys(oid INTEGER NOT NULL PRIMARY KEY);
Next, insert all of the records you're searching for into search_keys
table.
You might want to do an "ANALYZE" here on the temporary table, so the
planner can pick the most efficient way to do the upcoming join
depending on the number of rows you're searching for, and the number
of rows you're searching in.
Do the actual select:
SELECT ... from search_keys, target where search_keys.oid = bar.oid
I believe the ordering of the tables in the from clause may be
important as the planner may use it in determining which table to
process first, but if there are statistics about all of the tables it
may reorder them. drh, would know actual behavior for sure.
Finally don't forget to:
DROP TABLE search_keys
Cheers,
Rusty
--
Rusty Conover
InfoGears Inc.
Web: http://www.infogears.com
