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?
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
