the strange thing is that if you create
CREATE TABLE 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 car_plates_datetime ON car_plates(DateTime);
CREATE INDEX car_plates_plate ON car_plates(Plate);
and you do
PRAGMA case_sensitive_like=ON;
EXPLAIN QUERY PLAN SELECT * FROM car_plates WHERE ((Plate LIKE
'EX011%')) AND (DateTime>=1455058800000000)AND(DateTime<=1455231599999999);
you will see the use of car_plates_datetime not car_plates_plate, but if
you force the use of the index(car_plates_plate) it will use the correct
index
PRAGMA case_sensitive_like=ON;
EXPLAIN QUERY PLAN SELECT * FROM car_plates indexed by
car_plates_plateWHERE ((Plate LIKE 'EX011%')) AND
(DateTime>=1455058800000000)AND(DateTime<=1455231599999999)
with PRAGMA case_sensitive_like=OFF; you obviously obtain error
Selea s.r.l.
Michele Pradella R&D
SELEA s.r.l.
Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.pradella at selea.com* <mailto:michele.pradella at selea.com>
*http://www.selea.com*
Il 12/02/2016 13.20, Richard Hipp ha scritto:
> 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.
>
>
