it could be very nice to have a tool going through a bunch of queries, giving all the optimal indices... nothing to do with a brain, it is something that can be produced automatically. the final decision on to create them or not is belong to the implementer (the brain)
I know that I would be happy to have such a tool, but if no one have the need, then I will stay with my brain ... Thank you very much for your help & answer, Best regards, Sylvain On Tue, Nov 3, 2009 at 9:16 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
