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. <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> Regards Tim D. Richard Hipp wrote: > On Nov 16, 2009, at 5:14 PM, Tim Romano wrote: > >> Do I understand the docs correctly, that if the query statement >> contains >> a literal string (as distinct from a bound parameter) it doesn't >> matter >> which function is used to compile the statement and the index WILL be >> used provided all of the optimization prerequisites are met? >> >> > > > SQLite uses a cost-based query planner. The query planner looks at > many (hundreds, thousands) of ways of doing the query and selects the > one it thinks will get the job done fastest. If the LIKE term is what > seems likely (to SQLite) to give the fastest answer, then that is what > will be used. And if conditions are as you describe, then probably > the LIKE term will seem fastest to SQLite. But there are lots of > other considerations and hence no guarantees. > > One thing that SQLite does guarantee is that it will always make the > same query plan choice until you either (1) change the schema, (2) run > ANALYZE, (3) change the RHS of the LIKE operator, (4) compile with > SQLITE_ENABLE_STAT2 and change binding on other parameters in the > query, or (5) change SQLite release versions. So once you get it > using LIKE it should always continue using 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.67/2506 - Release Date: 11/16/09 > 07:43:00 > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
