the following simple query is very slow: SELECT title FROM t1 WHERE ipnode='VZ' ORDER BY author;
The database is about 250 MB in size and the table the query is run against has 12 cols and 120,000 rows. Every col has an index. The above query returns about 80% of the records. As soon as the ORDER BY statement is left away, the query ist fast.
SQLite only uses a single index per table on any give query. This is unlikely to change.
Since your query is returning 80% of the rows in the table, the use of an index to implement the WHERE clause is not really helping you any. But the simple-minded query optimizer of SQLite does not realize this. SQLite always prefers to use an index to implement the WHERE clause when it can. SQLite will also use that same index to implement ORDER BY, if possible, or if no index was helpful for implementing WHERE it will try to find an index to implement ORDER BY.
In your case, the best solution would be to trick sqlite into not using the index on the WHERE clause. This will make it use the index to implement ORDER BY and you should get much better performance. I suggest trying this query:
SELECT title FROM t1 WHERE ipnode||'x'='VZx' ORDER BY author;
By appending the string 'x' onto the end of the ipnode column prevents the query optimizer from use an index on ipnode. This leave the optimizer free to use an index to implement ORDER BY.
The other thing you would try is to DROP the index on the ipnode column.
-- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
