ok, assume casr_sensitive_like=OFF (default), according the point 6 of
LIKE optimization:
http://www.sqlite.org/optoverview.html
should be the same to have my table definition and
CREATE INDEX IF NOT EXISTS car_plates_plate ON car_plates(Plate COLLATE NOCASE);
Correct? because that way LIKE do not use index.
Anyway about the test case you propose, the first EXPLAIN do not use index, the
second (after PRAGMA case_sensitive_like=ON;) correctly use the index.
I tried the same in an DB I created month ago with the same structure...but it
doesn't work....probably the DB was created with a earlier sqlite version and I
do not know if this can cause the use of wrong index.
So speaking about performance, which is better PRAGMA case_sensitive_like=ON;
or PRAGMA case_sensitive_like=OFF;?
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.
>
>
