hi dennis now it works.
thanks as lot for your help. klaus -----Ursprüngliche Nachricht----- Von: Dennis Cote [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 25. Januar 2006 20:50 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] sqlite and index 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
