The patch doesn't attached, please apply link to the patch. 2012/7/12 Filip Navara <filip.nav...@gmail.com>
> 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 > > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
