On 3 Nov 2009, at 7:33pm, Sylvain Pointeau wrote:
> 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.
Your brain. A simplified explanation on how to decide what indices
you need follows.
Each SELECT command uses exactly one INDEX. For the SELECT to work
fastest you make sure there's an index which is an ideal helper for
the SELECT command. A normal SELECT command (one without special
things like JOIN and 'group') uses an index for three things:
SELECT rcol1, rcol2, rcol3 FROM table1
WHERE wcol1<88 AND wcol2>'DEF' and wcol3=208
ORDER BY ocol1, ocol2 DESC, ocol3
1) To reject all the records your SELECT does not want
2) To retrieve the records your SELECT /does/ want to the right order
3) To save the effort of having to read the row data from the actual
TABLE
If you are making up an index especially to suite a particular query
you start considering your variables from the beginning of the index ...
CREATE INDEX tab1_idx1 ON table1 (col1, col2, col3, col4, col5 ...)
The first columns you'll list will be the ones mentioned in your WHERE
clause.
The next columns you'll list will be the ones used in your ORDER BY
clause, in the order that that clause mentions them.
You might also list other columns which have values you want to
return, but there is a payoff in that between database size, the speed
your SELECT runs at, and the speed your INSERT and UPDATE commands run
at.
So without knowing anything about the table, or the values in it, for
the above SELECT I might guess that the best index would be
CREATE INDEX tab1_idx1 ON table1 (wcol1, wcol2, wcol3, ocol1, ocol2,
ocol3, rcol1, rcol2, rcol3)
but that's purely a guess. The ocols may be in a poor order. There
may be no point in listing the rcols. In fact there may be no point
in listing the ocols either.
It's worth noting that although the index is very important in making
the SELECT command run quickly, careful arrangement of a WHERE clause
can also make a big difference when the WHERE clause is complicated.
However, guessing the best arrangement for the WHERE clause cannot be
done without knowledge of example data in the table: it depends on
whether the data is clumped in only a few distinct values, or spread
evenly over a lot of different values.
You can read more about this, although it's not easy to understand
without experience, here:
<http://www.sqlite.org/optoverview.html>
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
