> SQLite only uses a single index per table on any give query. > This is unlikely to change.
Would it be able to use a multi-column query on ipnode + author? Hugh > Shi Elektronische Medien GmbH, Peter Spiske wrote: > > > > 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] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
