This roughly resembles an issue I witnessed on our databases about year ago (thread "Improving the query optimizer" on this mailing list). SQLite doesn't use covering index for fulfilling queries unless the query is filtered/ordered by a column included in the index. In many cases the covering index is actually smaller than the full table and thus it's less data to read.
We use a modified version of SQLite with the attached patch to alleviate the issue. Best regards, Filip Navara On Thu, Jul 12, 2012 at 12:59 PM, Richard Hipp <d...@sqlite.org> wrote: > On Wed, Jul 11, 2012 at 7:49 PM, Kohji Nakamura <k.nakam...@nao.ac.jp>wrote: > >> Hello all, >> >> I found that the access to an indexed column without "order by" is slower >> than the one with "order by" in SQLite 3071300. >> Using an index rather than an actual column is faster even if there is no >> need to use the index when the column has index. >> In general, to fetch column value, there is no need to access actual >> column when it has a dedicated index or it is a first column of composite >> index. >> I hope SQLite would do this optimization which is common to other DBMSs. >> >> Followings are the results of the comparison. Time column of main table >> has an index. >> >> After disk cache is cleared, >> SQL: select time from main order by time; >> Total : 38.1312 sec >> >> SQL: select time from main; >> Total : 95.395 sec >> > > Can you please send us the output of EXPLAIN QUERY PLAN for these two > queries on your schema? > > >> >> When data is cached, >> SQL: select time from main order by time; >> Total : 0.497981 sec >> >> SQL: select time from main; >> Total:: 0.925122 sec >> >> Thank you for developing a very cool DBMS, SQLite! >> Kohji Nakamura >> -- >> k.nakam...@nao.ac.jp http://www.nao.ac.jp/E/index.html >> National Astronomical Observatory of Japan >> >> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
