[sqlite] Multiple Column index

[Keith Medcalf]

Your indexes are badly designed.

You require the following two indexes:
CREATE INDEX indexAB ON test(DateTime,CarPlate);
CREATE INDEX indexBA ON test(CarPlate,DateTime);

The indexes:
> CREATE INDEX indexA ON test(DateTime);
> CREATE INDEX indexB ON test(CarPlate);
serve no useful purpose and should be dropped.

> -----Original Message-----
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Michele Pradella
> Sent: Friday, 19 February, 2016 01:07
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] Multiple Column index
> 
> Hi all, I have a question about using index with multiple column.
> Take this test case
> 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);
> 
> now if you do
> [1] -> EXLPAIN QUERY PLAN SELECT * FROM test WHERE (DateTime=0) AND
> (CarPlate='AA000BB')
> 
> you obtain:
> 0|0|0|SEARCH TABLE test USING COVERING INDEX indexAB (DateTime=? AND
> CarPlate=?)
> So it's good.
> 
> if you do
> 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 (or using both index
> combining two select) to cover both condition (DateTime>X AND
> DateTime<Y  and CarPlate = 'something' )? multicolumn index can be used
> only for [1] queries?
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users