Thanks once again. If the optimizer is unaffected by the choice of function to prepare the statement when a string literal is on the RHS, I have to come up with another theory to explain why three different front-ends for SQLite report a query plan that differs from the one SQLite3.EXE reports for a simple query with string literal:
explain query plan select * from myTable where myIndexedNOCASECollationVarcharColumn like 'foo%' SQLite3.exe says it will use the index (and it does use it). The others all report a full table scan will be carried out (and one is). < 100ms versus 40 seconds. Regards Tim Romano D. Richard Hipp wrote: > On Nov 16, 2009, at 7:02 PM, Tim Romano wrote: > > >> Thanks for the reply. Sorry, I didn't make my question clear >> enough. I >> was trying to find out if the following statement would be true: >> >> If the value after the LIKE operator is a string literal (as distinct >> from a bound parameter) then >> the choice of function to compile the statement will have no effect on >> the optimizer; >> i.e. the optimizer is compiler-function-agnostic when the value after >> the LIKE operator is a string literal. >> > > Correct. > > >> <docs>The LIKE optimization is not attempted if the right-hand side >> is a >> parameter and the statement was prepared using sqlite3_prepare() or >> sqlite3_prepare16() . </docs> >> >> > > > The reason for this is that if the RHS of the LIKE is a parameter, the > statement will need to be re-prepared whenever the parameter is re- > bound because SQLite has to know the actual text value of the RHS in > order to figure out if indices can be used or not. But re-prepare is > not possible with sqlite3_prepare() and sqlite3_prepare16() since > those routine do not store the original SQL text. > sqlite3_prepare_v2() and sqlite3_prepare16_v2(), on the other hand, do > store the original SQL text and can re-prepare and so they can > optimize for parameters on the RHS of a LIKE. > > D. Richard Hipp > d...@hwaci.com > > > > _______________________________________________ > 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.68/2507 - Release Date: 11/16/09 > 19:53:00 > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
