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
