• Welcome, Guest. Please login.
 
November 21, 2019, 03:47:48 am

News:

Welcome to the SQLitening support forums!


LIKE requires TEXT to use index

Started by cj, May 31, 2015, 10:56:35 am

Previous topic - Next topic

cj

Test indexes using SQLite explain statement
https://www.sqlite.org/eqp.html

To use indexes with LIKE it is important to Create table table1(column1 TEXT COLLATE NOCASE);

SQLitening example showing EXPLAIN QUERY PLAN:

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG 'like.bas

  LOCAL s() AS STRING, s AS STRING
  slOpen "sample.db3","C"

  slexe "drop table if exists t"
  slexe "create table t(c1 text)"
  slexe "create index i on t(c1)"
  slSelAry "explain query plan select * from t where c1 like 'Z%'",s(),"Q9c"
  ? JOIN$(s(),$CRLF),,"Index not used"
  'SCAN TABLE T

  slexe "drop table if exists t"
slexe "create table  t(c1 text collate nocase)"
  slexe "create index i on t(c1)"
  slSelAry "explain query plan select * from t where c1 like 'Z%'",s(),"Q9c"
  ? JOIN$(s(),$CRLF),,"Index used"
  'SEARCH TABLE t USING COVERING INDEX i (c1>? AND c1<?)
END FUNCTION