[sqlite] query Benchmark

On Mon, 15 Feb 2016 10:57:29 +0100 Michele Pradella wrote: > Sorry you are right, the test I did was with % not with a *just a > cut and paste error > Anyway it's tricky because I have to tell sqlite which index to use > in LIKE to fast search but I do not have to tell the index if start >

[sqlite] query Benchmark

Anyway thank you Simon the point of view it's clear now Selea s.r.l. Michele Pradella R 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*

[sqlite] query Benchmark

Sorry you are right, the test I did was with % not with a *just a cut and paste error Anyway it's tricky because I have to tell sqlite which index to use in LIKE to fast search but I do not have to tell the index if start with % or _ because otherwise the query is not executed.. I'll handle

[sqlite] query Benchmark

Probably is documented so I'm going to read carefully, anyway if you do this CREATE TABLE car_plates (Id INTEGER PRIMARY KEY AUTOINCREMENT,FileName VARCHAR(255),Plate VARCHAR(255)); CREATE INDEX car_plates_plate on car_plates(Plate); PRAGMA case_sensitive_like=ON; explain query plan SELECT *

[sqlite] query Benchmark

Hello MP> Anyway it's tricky because I have to tell sqlite which index to use in MP> LIKE to fast search but I do not have to tell the index if start with MP> % or _ because otherwise the query is not executed.. I'll handle it... I'm not sure if other people have suggested it already, but you

[sqlite] query Benchmark

On 15 Feb 2016, at 9:42am, Michele Pradella wrote: > CREATE TABLE car_plates (Id INTEGER PRIMARY KEY AUTOINCREMENT,FileName > VARCHAR(255),Plate VARCHAR(255)); > CREATE INDEX car_plates_plate on car_plates(Plate); > PRAGMA case_sensitive_like=ON; > > explain query plan SELECT * FROM

[sqlite] query Benchmark

On 2016/02/12 6:38 PM, Michele Pradella wrote: > Already solved with UNION of SELECT > It isn't solved, it is circumvented by trial and error without understanding why. I'm sure that works ok for you in this case, but the point is if you do study those documents a bit more, you may grasp the

[sqlite] query Benchmark

On 02/12/2016 03:47 PM, Michele Pradella wrote: > Hi all, is there a way to make a benchmark of queries to check which > version is faster? I'm using sqlite shell, the question is about on > how to make repetitive tests in the same conditions (for example I > need to totally disable cache to

[sqlite] query Benchmark

ite-users at mailinglists.sqlite.org> ??: Re: [sqlite] query Benchmark I'm already using my software. The scope of test my query with sqlite shell is to have a test environment without other interaction, just to test witch is the fast version of my query to use in my software. Another question is, if I have

[sqlite] query Benchmark

Already solved with UNION of SELECT Selea s.r.l. Michele Pradella R 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* *http://www.selea.com* Il 12/02/2016

[sqlite] query Benchmark

Splitting query in 2 SELECT using UNION let me use car_plates_plate index without problemvery strange...but I found a walkaround Selea s.r.l. Michele Pradella R SELEA s.r.l. Via Aldo Moro 69 Italy - 46019 Cicognara (MN) Tel +39 0375 889091 Fax +39 0375 889080

[sqlite] query Benchmark

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

[sqlite] query Benchmark

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

[sqlite] query Benchmark

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

[sqlite] query Benchmark

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

[sqlite] query Benchmark

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

[sqlite] query Benchmark

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

[sqlite] query Benchmark

Moreover if I make field LIKE 'AA%' and I use INDEXED BY index_on_field shell return me "Error: no query solution" so it's seams sqlite with LIKE operator can't use index on field Selea s.r.l. Michele Pradella R SELEA s.r.l. Via Aldo Moro 69 Italy - 46019 Cicognara (MN) Tel

[sqlite] query Benchmark

Ok understood...anyway trying with sqlite shell it's seams that (filed LIKE 'AA%') is slower than (field>='AAA' AND field<='AAZ') do you think there's a way I can check if the optimization is working? Selea s.r.l. Michele Pradella R SELEA s.r.l. Via Aldo Moro 69 Italy -

[sqlite] query Benchmark

Just to understand, if I have the query with LIKE simplified: SELECT field FROM car_plates WHERE (field LIKE 'EX011%'); I can't use index by field now suppose you know text value you can have in field, I can make the query: SELECT field FROM car_plates WHERE (field>='EX011A')AND(field<='EX011Z');

[sqlite] query Benchmark

On 2/12/16, Michele Pradella wrote: > 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

[sqlite] query Benchmark

I'm already using my software. The scope of test my query with sqlite shell is to have a test environment without other interaction, just to test witch is the fast version of my query to use in my software. Another question is, if I have a query like this: SELECT *field* FROM car_plates WHERE

[sqlite] query Benchmark

On 12 Feb 2016, at 10:14am, Michele Pradella wrote: > I can make the query: > SELECT field FROM car_plates WHERE (field>='EX011A')AND(field<='EX011Z'); > now I can use the index the query is faster. SQLite makes this optimization for you. See section 4.0 of

[sqlite] query Benchmark

On 12 Feb 2016, at 9:28am, Michele Pradella wrote: > I'm already using my software. The scope of test my query with sqlite shell > is to have a test environment without other interaction, just to test witch > is the fast version of my query to use in my software. Okay. I understand why you

[sqlite] query Benchmark

Hi all, is there a way to make a benchmark of queries to check which version is faster? I'm using sqlite shell, the question is about on how to make repetitive tests in the same conditions (for example I need to totally disable cache to avoid different results the second time query is

[sqlite] query Benchmark

On 12 Feb 2016, at 8:47am, Michele Pradella wrote: > Hi all, is there a way to make a benchmark of queries to check which version > is faster? I'm using sqlite shell, the question is about on how to make > repetitive tests in the same conditions (for example I need to totally > disable

[sqlite] query Benchmark

On 2/12/16, Michele Pradella 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

[sqlite] query Benchmark

On Behalf Of Simon Slavin > Sent: Friday, 12 February, 2016 03:24 > To: SQLite mailing list > Subject: Re: [sqlite] query Benchmark > > > On 12 Feb 2016, at 10:14am, Michele Pradella > wrote: > > > I can make the query: > > SELECT field FROM car_plates WHERE >

[sqlite] query Benchmark

On 2/12/16, Michele Pradella 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