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&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 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>=1455058800000000) AND (DateTime<=1455231599999999) 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&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 10.23, Simon Slavin ha scritto:
>> On 12 Feb 2016, at 8:47am, Michele Pradella
>> <michele.pradella at selea.com> 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
