Martin Engelschalk wrote: > > I have to select data from a large table (several million records) in > descending order and created an index for that purpose. However, sqlite > seems not to use this index for selecting the data. > > In the documentation of the "create index" - statement, i found the > following sentence: > > "Each column name can be followed by one of the "ASC" or "DESC" keywords > to indicate sort order, but the sort order is ignored in the current > implementation. Sorting is always done in ascending order." > > However, the news for Version 3.3.0 of Jan 2006 says: > > "Version 3.3.0 adds support for .... DESC indices". > > Is this a contradiction? Can sqlite use an index for order by ... desc - > clauses? Or am i doing sonething wrong? >
Martin, I suspect the create index documentation is out of date. SQLite can use either an ascending or descending order index to order records in either ascending or descending order. It simply scans the index from front to back, or back to front to get the required order. This shows that either index order can be used. sqlite> create table t1 (id, a); sqlite> create table t2 (id, a); sqlite> create index t1_a_asc on t1(a asc); sqlite> create index t2_a_dsc on t2(a desc); sqlite> explain query plan select * from t1 order by a desc; 0|0|TABLE t1 WITH INDEX t1_a_asc ORDER BY sqlite> explain query plan select * from t2 order by a desc; 0|0|TABLE t2 WITH INDEX t2_a_dsc ORDER BY If you post the actual SQL statements you use to create the table and index, and the query you are using, someone will be able to figure out why it isn't using the index. HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
