On Mon, 2 Sep 2013 17:32:31 +0100, Simon Slavin <slav...@bigfraud.org> wrote:
> >On 2 Sep 2013, at 3:58pm, Bert Huijben <rhuij...@apache.org> wrote: > >> We anticipate that the wc_id column will be used more in future versions and >> I had hoped that the assumption that a better index match (matching more >> usable columns) would always be preferable over one that uses less columns. > >If you want to test for good indexes and don't understand how indexing will >work on your data ... > >1) Put in a convincing set of data >2) Run ANALYZE >3) Make up a ton of indexes, indexing lots of tables in many different orders. >4) Find the SELECT and UPDATEs you run most often and use EXPLAIN QUERY PLAN >on them. >5) Keep the indexes the query plans mention, and drop the ones they don't >mention. I would reverse 2) and 3), otherwise ANALYZE has nothing to analyze. The query plans of 4) are only valid with results of ANALYZE. Without sqlite_stat1 , the SQLite optimizer may choose different indexes at runtime. So, this strategy only works if Bert decides to include a populated sqlite_stat1 table in the SVN init code. >This stuff can't be done by theory: there are too many >possibilities and it requires too good an understanding >of how the query planner works for non-experts. >There's no substitute for actual testing. > >Simon. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
