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.