[sqlite] query Benchmark

[Eduardo Morras]

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
> with % or _ because otherwise the query is not executed.. I'll handle
> it...

I have read the thread and still don't know what's your LIKE expression to 
search. If you know something about the string, you can use this tricks:

a) If you search for '%552', any string that ends with '552', you can create an 
index over reverse plate string and use that index,

b) If you search for '_552_' where the number of '_' before and after '552' are 
always the same (so, '552' begins at the same position on every plate), create 
a similar index to a) case, but rotating the letters on the string instead 
reversing it.

You know your data structure and the queries, perhaps other data transforms may 
create better indexs

> Selea s.r.l.
> 
> 
> Michele Pradella R

---   ---
Eduardo Morras 

[sqlite] query Benchmark

[Michele Pradella]

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* 
*http://www.selea.com*
Il 15/02/2016 10.51, Simon Slavin ha scritto:
> 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 car_plates INDEXED BY car_plates_plate 
>> WHERE (Plate LIKE '*552*'); //OK
>> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate 
>> WHERE (Plate LIKE '__552*'); //Error: no query solution
>> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate 
>> WHERE (Plate LIKE '_*552*'); //Error: no query solution
>> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate 
>> WHERE (Plate LIKE '*_552*'); /OK
>>
>> I think is a strange behavior, but I'm going to read documentation.
> An underline character _ in the LIKE field means "any character".  There is 
> no way for SQLite to use an index to find a string that starts with any 
> character.  The index provides no help at all.  The query will work perfectly 
> if you don't insist on using that index.
>
> By the way, I'm guessing from the above that you think the asterisk * means 
> "any string of characters".  It doesn't.  You should use the percent sign % 
> for that.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query Benchmark

[Michele Pradella]

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 it...

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 15/02/2016 10.51, Simon Slavin ha scritto:
> 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 car_plates INDEXED BY car_plates_plate 
>> WHERE (Plate LIKE '*552*'); //OK
>> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate 
>> WHERE (Plate LIKE '__552*'); //Error: no query solution
>> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate 
>> WHERE (Plate LIKE '_*552*'); //Error: no query solution
>> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate 
>> WHERE (Plate LIKE '*_552*'); /OK
>>
>> I think is a strange behavior, but I'm going to read documentation.
> An underline character _ in the LIKE field means "any character".  There is 
> no way for SQLite to use an index to find a string that starts with any 
> character.  The index provides no help at all.  The query will work perfectly 
> if you don't insist on using that index.
>
> By the way, I'm guessing from the above that you think the asterisk * means 
> "any string of characters".  It doesn't.  You should use the percent sign % 
> for that.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query Benchmark

[Michele Pradella]

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 * FROM car_plates INDEXED BY car_plates_plate  
WHERE (Plate LIKE '*552*'); //OK
explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate  
WHERE (Plate LIKE '__552*'); //Error: no query solution
explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate  
WHERE (Plate LIKE '_*552*'); //Error: no query solution
explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate  
WHERE (Plate LIKE '*_552*'); /OK

I think is a strange behavior, but I'm going to read documentation.

PS.I think another strange behavior is that if you use default COLLATION 
for the column and the default car_sensitive_like you will get always 
the slowest result in LIKE queries

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 13/02/2016 11.43, R Smith ha scritto:
>
>
> 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 reason too, and 
> perhaps make much better/faster queries in future without spending 
> hours on trial and error. Save yourself a lot of time, we think.
>
>
>
>> Il 12/02/2016 17.03, Richard Hipp ha scritto:
>>> ...//so that it is easier to understand. Perhaps a table that shows the
>>> various combinations of COLLATE and PRAGMA case_sensitive_like work
>>> together.  I dunno.  I'll take the action item to revisit the
>>> documentation and try to improve it.
>
> @Dr.Hipp: Would it be possible / difficult to perhaps improve the like 
> algorithm and pragma case-insensitive_like to take 3 values, such that 
> 0 = OFF, 1 = ON and 2 = AS PER COLUMN DEF?
>
> I realize the like function maybe doesn't know which column, nor is 
> the like operation guaranteed to happen on a column. Maybe there is 
> another way? Perhaps only from inside the query planner when making 
> plan decisions? (Currently it compares the ci-ness during planning to 
> decide on best index to use, if I'm not mistaken).
>
> What I have done is simply added a custom LIKE function that takes a 
> second parameter for case sensitivity to avoid pragma-juggling - but 
> by this, the query planner is none the wiser
>
> I find myself sometimes needing the above, though not often enough to 
> warrant requesting the feature, but it seems now maybe others have the 
> need too.
>
> Thanks,
> Ryan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query Benchmark

[Swithun Crowe]

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 can create
another field in your table to store the text you want to search reversed,
plus another index for this reversed field.

Then you can match rows using LIKE with the wildcard at the end of your
query while still using an index.

Swithun.

[sqlite] query Benchmark

[Simon Slavin]

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 car_plates INDEXED BY car_plates_plate WHERE 
> (Plate LIKE '*552*'); //OK
> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate WHERE 
> (Plate LIKE '__552*'); //Error: no query solution
> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate WHERE 
> (Plate LIKE '_*552*'); //Error: no query solution
> explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate WHERE 
> (Plate LIKE '*_552*'); /OK
> 
> I think is a strange behavior, but I'm going to read documentation.

An underline character _ in the LIKE field means "any character".  There is no 
way for SQLite to use an index to find a string that starts with any character. 
 The index provides no help at all.  The query will work perfectly if you don't 
insist on using that index.

By the way, I'm guessing from the above that you think the asterisk * means 
"any string of characters".  It doesn't.  You should use the percent sign % for 
that.

Simon.

[sqlite] query Benchmark

[R Smith]

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 reason too, and 
perhaps make much better/faster queries in future without spending hours 
on trial and error. Save yourself a lot of time, we think.



> Il 12/02/2016 17.03, Richard Hipp ha scritto:
>> ...//so that it is easier to understand. Perhaps a table that shows the
>> various combinations of COLLATE and PRAGMA case_sensitive_like work
>> together.  I dunno.  I'll take the action item to revisit the
>> documentation and try to improve it.

@Dr.Hipp: Would it be possible / difficult to perhaps improve the like 
algorithm and pragma case-insensitive_like to take 3 values, such that 0 
= OFF, 1 = ON and 2 = AS PER COLUMN DEF?

I realize the like function maybe doesn't know which column, nor is the 
like operation guaranteed to happen on a column. Maybe there is another 
way? Perhaps only from inside the query planner when making plan 
decisions? (Currently it compares the ci-ness during planning to decide 
on best index to use, if I'm not mistaken).

What I have done is simply added a custom LIKE function that takes a 
second parameter for case sensitivity to avoid pragma-juggling - but by 
this, the query planner is none the wiser

I find myself sometimes needing the above, though not often enough to 
warrant requesting the feature, but it seems now maybe others have the 
need too.

Thanks,
Ryan

[sqlite] query Benchmark

[Dan Kennedy]

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 avoid different results the second 
> time query is executed).
> I try to explain better: the same query re-arranged in two different 
> queries to check which is the faster, executed on the same DB, how can 
> I do speed check of those queries avoiding interaction between two 
> queries(example cache)?

One way to look at performance of an SQLite query is to say that it is 
influenced by three factors: the amount of IO, the number and size of 
malloc() calls made and the CPU consumed by the library to run the query.

Adding the -stats option to the shell tool command line causes it to 
output some helpful information after running each query. Including page 
cache hits and misses. And some stuff about malloc() usage.

We usually use the valgrind tool callgrind or cachegrind to repeatably 
measure CPU load here.

Dan.

[sqlite] query Benchmark

[Quan Yong Zhai]

http://sqlite.org/rtree.html

To improve your query performance, you need a r-tree index on DateTime

???: Michele Pradella<mailto:michele.pradella at selea.com>
: ?2016/?2/?12 17:28
???: sqlite-users at mailinglists.sqlite.org<mailto:sqlite-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 a query like this:
SELECT *field* FROM car_plates WHERE (*field* LIKE 'EX011%') AND
(DateTime>=14550588) AND (DateTime<=14552315) ORDER BY
DateTime;

If I try to force the use of an index on *field *but I think sqlite
can't use it, is it right?

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* <mailto:michele.pradella at selea.com>
*http://www.selea.com*
Il 12/02/2016 10.23, Simon Slavin ha scritto:
> 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 cache to avoid different results the second time query is executed).
> Using the ".timer ON" command in the command-line shell, as you write, is the 
> best way to do timing.  But there are many levels of cache in your computer 
> and there's no way to disable them all.  You will definitely get timings 
> influenced by interactions.
>
> It looks like you are trying to make commands run as fast as possible.  
> Unless you are writing an academic paper on theory, this is probably the 
> wrong thing to do.  Write your software and see if it runs fast enough.  Only 
> if it runs too slowly do you need to start worrying about speed.
>
> We can advise on good SQL commands and good indexes for you if you do need to 
> increase your speed.
>
> Simon.
> ___
> 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

[sqlite] query Benchmark

[Michele Pradella]

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 17.03, Richard Hipp ha scritto:
> 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 me "Error: no query solution"
>>
>> if I remove the second expression ((CarPlateType==-1)AND((Plate LIKE
>> '~A00O%'))) it works
>>
> All of your questions are answered in the documentation, in the links
> provided in previous replies.  The fact that you are still asking
> questions tells me that I need to work on improving the documentation
> so that it is easier to understand.  Perhaps a table that shows the
> various combinations of COLLATE and PRAGMA case_sensitive_like work
> together.  I dunno.  I'll take the action item to revisit the
> documentation and try to improve it.
>
> Meanwhile, please go reread the documentation yourself, slowly and
> carefully, and see if this doesn't all suddenly make sense.

[sqlite] query Benchmark

[Michele Pradella]

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
*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
>
>
>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 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>=14550588)AND(DateTime<=14552315);
>>
>> 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>=14550588)AND(DateTime<=14552315)
>>
>> with PRAGMA case_sensitive_like=OFF; you obviously obtain error
>>
>> 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 13.20, Richard Hipp ha scritto:
>>> 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 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>=14550588) AND
>>> (DateTime<=14552315) ORDER BY DateTime DESC LIMIT 2;
>>> .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>=14550588) AND
>>> (DateTime<=14552315) ORDER BY DateTime DESC LIMIT 2;
>>>
>>> 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] query Benchmark

[Michele Pradella]

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


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 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>=14550588)AND(DateTime<=14552315);
>
> 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>=14550588)AND(DateTime<=14552315)
>
> with PRAGMA case_sensitive_like=OFF; you obviously obtain error
>
> 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 13.20, Richard Hipp ha scritto:
>> 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 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>=14550588) AND
>> (DateTime<=14552315) ORDER BY DateTime DESC LIMIT 2;
>> .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>=14550588) AND
>> (DateTime<=14552315) ORDER BY DateTime DESC LIMIT 2;
>>
>> 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] query Benchmark

[Michele Pradella]

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>=14550588)AND(DateTime<=14552315);

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>=14550588)AND(DateTime<=14552315)

with PRAGMA case_sensitive_like=OFF; you obviously obtain error

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 13.20, Richard Hipp ha scritto:
> 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 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>=14550588) AND
> (DateTime<=14552315) ORDER BY DateTime DESC LIMIT 2;
> .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>=14550588) AND
> (DateTime<=14552315) ORDER BY DateTime DESC LIMIT 2;
>
> 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] query Benchmark

[Michele Pradella]

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>=14550588) AND
(DateTime<=14552315) ORDER BY DateTime DESC LIMIT 2;

the index selected is the one on DateTime
0|0|0|SEARCH TABLE car_plates USING INDEX car_plates_datetime 
(DateTime>? AND DateTimemailto: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 workprobably 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
>
>
>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 13.20, Richard Hipp ha scritto:
>> 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 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>=14550588) AND
>> (DateTime<=14552315) ORDER BY DateTime DESC LIMIT 2;
>> .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>=14550588) AND
>> (DateTime<=14552315) ORDER BY DateTime DESC LIMIT 2;
>>
>> 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] query Benchmark

[Michele Pradella]

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 workprobably 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


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 13.20, Richard Hipp ha scritto:
> 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 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>=14550588) AND
> (DateTime<=14552315) ORDER BY DateTime DESC LIMIT 2;
> .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>=14550588) AND
> (DateTime<=14552315) ORDER BY DateTime DESC LIMIT 2;
>
> 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] query Benchmark

[Michele Pradella]

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>=14550588) AND 
(DateTime<=14552315) ORDER BY DateTime DESC LIMIT 2;

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


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 13.06, Richard Hipp ha scritto:
> 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 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.

[sqlite] query Benchmark

[Michele Pradella]

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?

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 11.23, Simon Slavin ha scritto:
> 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
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query Benchmark

[Michele Pradella]

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 +39 0375 889091
Fax +39 0375 889080
*michele.pradella at selea.com* 
*http://www.selea.com*
Il 12/02/2016 11.39, Michele Pradella ha scritto:
> 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 - 46019 Cicognara (MN)
> Tel +39 0375 889091
> Fax +39 0375 889080
> *michele.pradella at selea.com* 
> *http://www.selea.com*
> Il 12/02/2016 11.23, Simon Slavin ha scritto:
>> 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
>>
>> 
>>
>> Simon.
>> ___
>> 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

[sqlite] query Benchmark

[Michele Pradella]

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 - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.pradella at selea.com* 
*http://www.selea.com*
Il 12/02/2016 11.23, Simon Slavin ha scritto:
> 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
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query Benchmark

[Michele Pradella]

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');
now I can use the index the query is faster. Do you think the results 
should be the same (consider field that can have only 2 letters after 
EX011)?

generalizing the behavior is (field LIKE 'EX011%') tha same as 
(field>='EX011*lowestASCII*')AND(field<='EX011*greatestASCII*')?

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 10.28, Michele Pradella ha scritto:
> 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 (*field* LIKE 'EX011%') AND 
> (DateTime>=14550588) AND (DateTime<=14552315) ORDER BY 
> DateTime;
>
> If I try to force the use of an index on *field *but I think sqlite 
> can't use it, is it right?
>
> 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 10.23, Simon Slavin ha scritto:
>> 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 cache to avoid different results the second 
>>> time query is executed).
>> Using the ".timer ON" command in the command-line shell, as you 
>> write, is the best way to do timing.  But there are many levels of 
>> cache in your computer and there's no way to disable them all.  You 
>> will definitely get timings influenced by interactions.
>>
>> It looks like you are trying to make commands run as fast as 
>> possible.  Unless you are writing an academic paper on theory, this 
>> is probably the wrong thing to do.  Write your software and see if it 
>> runs fast enough.  Only if it runs too slowly do you need to start 
>> worrying about speed.
>>
>> We can advise on good SQL commands and good indexes for you if you do 
>> need to increase your speed.
>>
>> Simon.
>> ___
>> 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

[sqlite] query Benchmark

[Richard Hipp]

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 me "Error: no query solution"
>
> if I remove the second expression ((CarPlateType==-1)AND((Plate LIKE
> '~A00O%'))) it works
>

All of your questions are answered in the documentation, in the links
provided in previous replies.  The fact that you are still asking
questions tells me that I need to work on improving the documentation
so that it is easier to understand.  Perhaps a table that shows the
various combinations of COLLATE and PRAGMA case_sensitive_like work
together.  I dunno.  I'll take the action item to revisit the
documentation and try to improve it.

Meanwhile, please go reread the documentation yourself, slowly and
carefully, and see if this doesn't all suddenly make sense.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] query Benchmark

[Michele Pradella]

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 (*field* LIKE 'EX011%') AND 
(DateTime>=14550588) AND (DateTime<=14552315) ORDER BY 
DateTime;

If I try to force the use of an index on *field *but I think sqlite 
can't use it, is it right?

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 10.23, Simon Slavin ha scritto:
> 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 cache to avoid different results the second time query is executed).
> Using the ".timer ON" command in the command-line shell, as you write, is the 
> best way to do timing.  But there are many levels of cache in your computer 
> and there's no way to disable them all.  You will definitely get timings 
> influenced by interactions.
>
> It looks like you are trying to make commands run as fast as possible.  
> Unless you are writing an academic paper on theory, this is probably the 
> wrong thing to do.  Write your software and see if it runs fast enough.  Only 
> if it runs too slowly do you need to start worrying about speed.
>
> We can advise on good SQL commands and good indexes for you if you do need to 
> increase your speed.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query Benchmark

[Simon Slavin]

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



Simon.

[sqlite] query Benchmark

[Simon Slavin]

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 want to do this but unfortunately it will not work. 
 You will always have cache interactions and it is extremely difficult to get 
'pretend' figures which ignore them.

> Another question is, if I have a query like this:
> SELECT *field* FROM car_plates WHERE (*field* LIKE 'EX011%') AND 
> (DateTime>=14550588) AND (DateTime<=14552315) ORDER BY 
> DateTime;
> 
> If I try to force the use of an index on *field *but I think sqlite can't use 
> it, is it right?

It might be bad to use an index based on *field* for this.  It might be better 
to use one which started with DateTime since that would provide both the 
selection of DateTime within a range, and also the ORDER BY clause.  But it 
would be best of all to let SQLite choose, since it knows more about SQLite 
than you do.  So don't force any index.

If you want to test out something like this, do the following in the shell tool:

CREATE INDEX cp_fd ON car_plates (field,DateTime);
CREATE INDEX cp_df ON car_plates (DateTime,field);
ANALYZE;
EXPLAIN QUERY PLAN SELECT *field* FROM car_plates WHERE (*field* LIKE 'EX011%') 
AND (DateTime>=14550588) AND (DateTime<=14552315) ORDER BY 
DateTime;

The output from EXPLAIN QUERY PLAN will tell you which index it has chosen to 
use.  Then you can delete the other one.

For ANALYZE to give useful results you need to have typical data in your 
tables.  SQLite will look at how 'chunky' this data is and use that to make its 
decisions.  You can run the commands with empty tables and they may not lead to 
the best results.

Simon.

[sqlite] query Benchmark

[Michele Pradella]

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 executed).
I try to explain better: the same query re-arranged in two different 
queries to check which is the faster, executed on the same DB, how can I 
do speed check of those queries avoiding interaction between two 
queries(example cache)?

[sqlite] query Benchmark

[Simon Slavin]

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 cache to avoid different results the second time query is executed).

Using the ".timer ON" command in the command-line shell, as you write, is the 
best way to do timing.  But there are many levels of cache in your computer and 
there's no way to disable them all.  You will definitely get timings influenced 
by interactions.  

It looks like you are trying to make commands run as fast as possible.  Unless 
you are writing an academic paper on theory, this is probably the wrong thing 
to do.  Write your software and see if it runs fast enough.  Only if it runs 
too slowly do you need to start worrying about speed.

We can advise on good SQL commands and good indexes for you if you do need to 
increase your speed.

Simon.

[sqlite] query Benchmark

[Richard Hipp]

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 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>=14550588) AND
(DateTime<=14552315) ORDER BY DateTime DESC LIMIT 2;
.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>=14550588) AND
(DateTime<=14552315) ORDER BY DateTime DESC LIMIT 2;

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.


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] query Benchmark

[Keith Medcalf]

If and only If you follow rule 6 and declare the column with a collation 
sequence that matches the collation in effect for the LIKE operator.

So if the column/index is declared with CASE SENSITIVE collation (BINARY, the 
default), and you use like on that column, and the LIKE is case insensitive 
(the default) then the index will not be used to optimize this constraint.  You 
must change the collations somewhere so that they match.

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] 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
> (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
> 
> <https://www.sqlite.org/optoverview.html>
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query Benchmark

[Richard Hipp]

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 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.
-- 
D. Richard Hipp
drh at sqlite.org