On 2/12/16, Michele Pradella <michele.pradella at selea.com> wrote: > table: > CREATE TABLE IF NOT EXISTS car_plates (Id INTEGER PRIMARY KEY > AUTOINCREMENT,DateTime BIGINT,StringKey VARCHAR(255) UNIQUE,Plate > VARCHAR(255),Type BIGINT,CameraName VARCHAR(255),CameraIP > VARCHAR(255),CameraMAC VARCHAR(255),FileName VARCHAR(255),Country > VARCHAR(255),Confidence VARCHAR(255),Reason VARCHAR(255),CarPlateType > BIGINT,VehicleType BIGINT,GPS VARCHAR(255)) > > index: > CREATE INDEX IF NOT EXISTS car_plates_plate ON car_plates(Plate) >
Test case: /* Your original schema */ CREATE TABLE IF NOT EXISTS car_plates (Id INTEGER PRIMARY KEY AUTOINCREMENT,DateTime BIGINT,StringKey VARCHAR(255) UNIQUE,Plate VARCHAR(255),Type BIGINT,CameraName VARCHAR(255),CameraIP VARCHAR(255),CameraMAC VARCHAR(255),FileName VARCHAR(255),Country VARCHAR(255),Confidence VARCHAR(255),Reason VARCHAR(255),CarPlateType BIGINT,VehicleType BIGINT,GPS VARCHAR(255)); CREATE INDEX IF NOT EXISTS car_plates_plate ON car_plates(Plate); /* Sample query with PRAGMA case_sensitive_like=OFF (the default) */ EXPLAIN QUERY PLAN SELECT DateTime,FileName,Plate,Type,CameraName,Id,Country,Reason,CarPlateType,VehicleType,GPS FROM car_plates WHERE ((Plate LIKE 'EX011%')) AND (Type IN (1,2,3,6,7,8)) AND (DateTime>=1455058800000000) AND (DateTime<=1455231599999999) ORDER BY DateTime DESC LIMIT 20000; .print ----- /* Sample Query with PRAGMA case_sensitive_like=ON */ PRAGMA case_sensitive_like=ON; EXPLAIN QUERY PLAN SELECT DateTime,FileName,Plate,Type,CameraName,Id,Country,Reason,CarPlateType,VehicleType,GPS FROM car_plates WHERE ((Plate LIKE 'EX011%')) AND (Type IN (1,2,3,6,7,8)) AND (DateTime>=1455058800000000) AND (DateTime<=1455231599999999) ORDER BY DateTime DESC LIMIT 20000; Run the above in the shell. See that the second query uses the index. Or, leave case_sensitive_like turned off (the default) but change the table definition to include: ... Plate VARCHAR(255) COLLATE nocase, ... If you add the "COLLATE nocase" to the example above, you will see that the index is used in the default setting, but not when PRAGMA case_sensitive_like=ON. -- D. Richard Hipp drh at sqlite.org
