Splitting query in 2 SELECT using UNION let me use car_plates_plate
index without problem....very strange...but I found a walkaround
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 16.43, Michele Pradella ha scritto:
> Why this query
> PRAGMA case_sensitive_like=ON;EXPLAIN QUERY PLAN SELECT * FROM
> car_plates INDEXED BY car_plates_plate
> WHERE ((CarPlateType!=-1)AND((Plate LIKE 'AA00O%')OR(Plate LIKE
> 'AA0O0%')))OR((CarPlateType==-1)AND((Plate LIKE '~A00O%')))
>
> give me "Error: no query solution"
>
> if I remove the second expression ((CarPlateType==-1)AND((Plate LIKE
> '~A00O%'))) it works
>
> 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 15.02, Michele Pradella ha scritto:
>> 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.
>>>
>>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
