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)
query sample:
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;
Anyway I tried even with :
SELECT
DateTime,FileName,Plate,Type,CameraName,Id,Country,Reason,CarPlateType,VehicleType,GPS
FROM car_plates WHERE ((Plate LIKE 'EX011%'))
SELECT
DateTime,FileName,Plate,Type,CameraName,Id,Country,Reason,CarPlateType,VehicleType,GPS
FROM car_plates INDEXED BY car_plates_plate WHERE ((Plate LIKE 'EX011%'))
give me 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.06, Richard Hipp ha scritto:
> On 2/12/16, Michele Pradella <michele.pradella at selea.com> wrote:
>> I try using COLLATE NOCASE index with PRAGMA case_sensitive_like=OFF;
>> and COLLATE BINARY index with PRAGMA case_sensitive_like=ON;
>> but I have always the query with field LIKE 'AA%' that can't use index
>> on field "No Query solution" is reported by shell if you try to force
>> index. And with explain query plan the index on field is not used.
>> Do you think I'm doing something wrong?
>>
> Yes I do.
>
> Please post your schema and your query and we will have a look.
