There is an excellent FAQ [http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html] for tweaking your use of an [SQLite] database for optimal performance. Almost all the suggestions in the FAQ are applicable to code using the Tcl binding, but they are not actually expressed in Tcl code snippets.  Hence this page, which attempts to do so.

----

In "Section 5: Optimizing Queries" the suggestion is made to replace GLOB and LIKE where possible.  The example given in the FAQ is "The expression (x GLOB 'abc*') can be replaced by (x >= 'abc' AND x < 'abd')."  In other words, if you have an INDEX on ''col_name'', and you want to match the first few characters of the values in ''col_name'', replace this:

 proc search_partial_match_slowly {str} {
     append str %
     dbcmd eval {SELECT * FROM table_name WHERE col_name LIKE $str} result {
         parray result
     }
 }

with this:

 proc search_partial_match_quickly {str} {
     set sub_str [string range $str 0 end-1]
     set last_char [string index $str end]
     set end $sub_str[format %c [expr {1+[scan $last_char %c]}]]
     dbcmd eval {SELECT * FROM table_name WHERE (col_name >= $str AND col_name < $end)} result {
         parray result
     }
 }

for greatly increased speed. (Time taken was reduced from much longer than one minute to much less than one second, in my case.) [Alastair Davies] 14 December 2006

----
SQLite does the above optimization automatically.
See http://www.sqlite.org/optoverview.html#like_opt
But the optimization is not valid for LIKE since LIKE is case insensitive
by default.  But if you turn on case sensitivity for LIKE, or if you make
the collating sequence of the column case insensitive, or if you use GLOB
instead of LIKE (since GLOB is case sensitive) then the optimization should
work for you.


----
[[[Category Database]|[Category Example]|[Category Performance]]]