[sqlite] Multiple Column index

[Clemens Ladisch]

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