No...I do not understand, try dump file and re-import but the result
it's always the same....
even if I use PRAGMA case_sensitive_like=ON;
the query
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;
the index selected is the one on DateTime....
0|0|0|SEARCH TABLE car_plates USING INDEX car_plates_datetime
(DateTime>? AND DateTime<?)
0|0|0|EXECUTE LIST SUBQUERY 1
I have to dig more about it
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.44, Michele Pradella ha scritto:
> 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.
>>
>>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
