On Sun, Nov 15, 2009 at 1:19 PM, Tim Romano <tim.rom...@yahoo.com> wrote: > Thanks for the reply. > > A follow question: I can understand why ... myColumn LIKE "%foo%" > ... would have to do a full scan but shouldn't ...myColumn LIKE > "foo%" ... be able to use an index? > >
see Section 4 in http://www.sqlite.org/optoverview.html for all your questions re. LIKE. > P Kishor wrote: >> On Sun, Nov 15, 2009 at 10:39 AM, Tim Romano <tim.rom...@yahoo.com> wrote: >> >>> I have a query with joined inline views that runs in about 100ms against >>> a 4 million row table joined to a 275,000 row table. Not bad, SQLite :-) >>> >>> But when I use the LIKE operator instead of the = operator, the order of >>> the query plan changes, though the same indexes are involved, and the >>> query takes 40 seconds. I'm trying to figure out what, if anything, I >>> can do to guide SQLite here. >>> >>> In broad terms, what is it about the use of the LIKE operator that >>> causes SQLite to re-order the plan, and is there any way to guide? >>> >>> >> >> LIKE doesn't use indexes, although there are tricks that these SQL >> gurus will probably tell that could help you with workarounds. LIKE >> does a full scan. >> >> >> >>> And what does the "from" column in the explain plan results refer to? >>> Are the values the tables/relations in the query statement? If so, how >>> are they mapped? In order of appearance in the statement, so that 0 is >>> the first table mentioned in the statement? >>> >>> Thanks >>> >>> >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >> >> >> >> >> ------------------------------------------------------------------------ >> >> >> No virus found in this incoming message. >> Checked by AVG - www.avg.com >> Version: 8.5.425 / Virus Database: 270.14.66/2504 - Release Date: 11/15/09 >> 07:50:00 >> >> > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= Sent from Madison, Wisconsin, United States _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
