Hi, many thanks for your answer,
I just think that it could be useful to know if an index is optimal or not, kind of a tool that could give you the best index for speed up a query. Many thanks, Sylvain On Tue, Nov 3, 2009 at 2:52 PM, Griggs, Donald <donald.gri...@allscripts.com > wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
