On 01/17/2013 09:17 PM, Selen Schabenberger wrote:
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?
Don't worry about it for now. Changes to the optimizer are things
that need to be considered really carefully. It's too easy to
improve one type of query to the detriment of others.
Dan.
-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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
