Klaus Schären wrote:
Hi
i have the following table definition:
CREATE TABLE Pistor (Nummer INTEGER PRIMARY KEY NOT NULL, Artikel TEXT NOT
NULL DEFAULT '', Inhalt TEXT NOT NULL DEFAULT '')
In addition, I have created the following index:
CREATE UNIQUE INDEX xPistor ON PISTOR(ARTIKEL, NUMMER)
when i run a query with "order by artikel", sqlite returns the result very
fast.
Query: SELECT ARTIKEL, NUMMER FROM PISTOR ORDER BY ARTIKEL
when i run a quey with "order by artikel, nummer" (witch is exactly the
index xPistor!), sqlite takes longer.
Query: SELECT ARTIKEL, NUMMER FROM PISTOR ORDER BY ARTIKEL, NUMMER
i run the explain command with both querys. with the second query, sqlite
makes a sort. is this ok? why does sqlite not use the index xPistor?
i try the query with different versions of sqlite (3.2.8, 3.3.1 and 3.3.2).
I have also running the ANALYZE command. but nothing is changing.
the test db is included in the attached zip.
thanks for your help
K. Schären
Klaus,
You have defined the field Nummer as a primary key. Therefore, each row
will have a unique value of Nummer. Your second index will enforce a
rule that all pairs of Artikel and Nummer must be unique, but that must
always be true since all values of Nummer are unique, regardless of the
value of Artikel.
The fact that Nummer is a primary key seems to confuse the optimizer for
this query. If I remove the primary key part of the definition from the
field Nummer, and use the explicit unique index you defined, then the
second query uses that index. When I do this:
CREATE TABLE Pistor2 (Nummer INTEGER NOT NULL, Artikel TEXT NOT
NULL DEFAULT '', Inhalt TEXT NOT NULL DEFAULT '')
CREATE UNIQUE INDEX xPistor2 ON PISTOR2(ARTIKEL, NUMMER)
insert into pistor2 select * from pistor
explain query plan SELECT ARTIKEL, NUMMER FROM PISTOR ORDER BY ARTIKEL, NUMMER
the query plan shows that the index is being used, and an explain
command shows that the sort is not done.
I think you have found an issue (I won't call it a bug because the query
does work correctly) with the optimizer. Your first query uses the index
because the output list is not ordered by Nummer, so the implicit index
on Nummer is not of any use, and the optimizer then find the other index
and uses it. The second query seems to think that it can use the
implicit index on Nummer to do part of the ordering, and since SQLite
only uses one index per table scan it can't use the explicit index. Then
it determines that it needs to do a sort to order by Artikel. My change
removes the implicit index on Nummer, so then it finds and uses the
explicit index to scan the table in the correct order.
HTH
Dennis Cote
