I was trying to avoid the INDEXED BY or the "+" operator as I cannot make sure that the search with the index will always (for every database created by my application) be faster than with the PK. I prefer the optimizer does the cost calculations and makes the choice.
Do you want me to write another ticket for the enhancement you mentioned? -Selen >________________________________ > From: Dan Kennedy <danielk1...@gmail.com> >To: General Discussion of SQLite Database <sqlite-users@sqlite.org> >Sent: Thursday, January 17, 2013 2:38 PM >Subject: Re: [sqlite] Multi-column index is not used with IN operator > >On 01/17/2013 06:32 PM, Selen Schabenberger wrote: >> >> Dan, I have just realised that with the sqlite_stat3 table, the query >> optimizer uses the INDEX IDX_TAG. When I drop this table, the PK is used >> as you said. But the concatenated index is still not used with the stat3. > >Looks like with stat3 enabled the estimate of the number of rows matched >by the WHERE clause is better. So it uses INDEX_IDX_TAG. But not the >composite index as the stat3 estimation is disabled if the query uses >more than the first column of the index (in this case it uses two - >'Tag' and 'Flag'). That's probably something that could be enhanced at >some point. > >> Knowing that the query could be 60 times faster, is there anything I can >> do to speed up this query or you think it is a bug in the optimizer? > >You could figure out why the stat1 data is deceptive in this case and >whether there is anything that can be done about it. > >Or add an INDEXED BY clause to the query. > >Adding a unary '+' operator in front of 'Id' might work too. > >Dan. > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
