On 2016/08/22 9:51 AM, Dominique Devienne wrote:
On Sun, Aug 21, 2016 at 6:26 PM, Igor Tandetnik <i...@tandetnik.org> wrote:
Create your index on nn(rsid, gene). Order of the fields matters. Imagine
a phonebook sorted by last name then first name. It's easy to find in it
all people named John Smith, or all people with last name of Smith, but
it's of no help when looking for all people named John.
Well, there's still https://www.sqlite.org/optoverview.html#skipscan
If the phonebook contains few last names, and many first names,
SQLite might in some circumstances still be able to use that (lastname,
firstname) index,
when looking for a given firstname. --DD
True, but ensure you have at least SQLite version 3.8.7 or higher (where
middle-term skip-scanning was enabled)
https://www.sqlite.org/releaselog/3_8_7.html
AND be sure you have run ANALYZE on the table so the Query planner knows
it can use the skip-scan automization. (this might be required on a
timely schedule)
Else, best practice is making the second Index on "gene".
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users