> 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.
So if I have a query like this
"SELECT * FROM table_name WHERE DateTime<VALUE_MAX AND DateTime>VALUE_MIN"
do you think I can use indexA or indexAB and the results in terms of
performance are the same? is there some "more work" that query have to
do if we use indexAB instead index?
>
>> -----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
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
