Hi Sylvain,
Regarding: can you describe an output of a "explain"?
==> A very short answer could be that, in the output of EXPLAIN QUERY
PLAN
"If you see the name of an index, then that index is used."
Regarding: is there something to tell us the best index to have for a
specified query?
==> Your question might be interpreted as:
a) Given the indexes defined in my schema, which ones are ideal to
use for a specific query?
Or
b) How do I create ideal indexes?
For each of these questions, the answer is "Both skill and craft are
sometimes involved, therefore there will often not be an absolute rule."
For question "a" (which to use), sqlite itself tries to determine this,
and often does a very good job. In some cases, the ANALYZE command
helps sqlite make these decisions.
For question "b" (how to create ideal indices) I am definitely not an
expert, but I think some general guidance might be:
-- For a given SELECT, sqlite will use, at most, one index per table.
-- An index on a large table is usually more useful than an index on
a tiny one.
-- An index on a column with many repeated values (low specificity)
may be less useful.
-- Compound indexes are sometimes used to good effect, but remember
that they are used from left to right
-- Your own testing in your particular database will give the most
authoritative answers.
-- This list is for sqlite, but I suspect you'll want to seek out
general SQL books and other resources.
Maybe this helps,
Donald
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
