because at least one field have to do with operator = correct?
no can be one of = or IN or IS but not LIKE operator
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 19/02/2016 09.41, Michele Pradella ha scritto:
> Ok understand, so there's no way to use that kind of double column
> index on a select like
> explain query plan select * from test where (CarPlate LIKE 'AA000%')
> AND (DateTime>1);
> because at least one field have to do with operator = correct?
>
> 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 19/02/2016 09.35, Clemens Ladisch ha scritto:
>> Michele Pradella wrote:
>>> CREATE TABLE test (DateTime BIGINT,CarPlate VARCHAR(255));
>>> CREATE INDEX indexA ON test(DateTime);
>>> CREATE INDEX indexB ON test(CarPlate);
>>> CREATE INDEX indexAB ON test(DateTime,CarPlate);
>>>
>>> EXLPAIN QUERY PLAN SELECT * FROM test WHERE
>>> (DateTime>1)AND(DateTime<100) AND (CarPlate = 'AA000BB');
>>> 0|0|0|SEARCH TABLE test USING INDEX indexB (CarPlate=?)
>>> So is used only the indexB not the index for DateTime, and if you
>>> force it indexAB
>>> 0|0|0|SEARCH TABLE test USING COVERING INDEX indexAB (DateTime>? AND
>>> DateTime<?)
>>> so it used only for DateTime.
>>>
>>> Do you think Is there a way to use indexAB to cover both condition?
>> The documentation <http://www.sqlite.org/optoverview.html#where_clause>
>> says:
>> | The initial columns of the index must be used with the = or IN or IS
>> | operators. The right-most column that is used can employ inequalities.
>>
>> So the column with the inequalities (DateTime) must be the right-most
>> one in the index:
>>
>> CREATE INDEX indexBA ON test(CarPlate, DateTime);
>>
>>
>> Regards,
>> Clemens
>> _______________________________________________
>> 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
