I supposed your SQL command like this
"select * from table where (DateTime>X AND
DateTime<Y and CarPlate = Z ) "
OR this
"select * from table where (DateTime>X AND
DateTime<Y and CarPlate Like 'AA00%')"
All these SQL can transform to
" select * from table where (DateTime between D1 and D2 and CarPlate between C1
and C2)"
Obviously, to speed up the query, you need a two-dimension r-tree index, one
dimension for datetime , another for carplate, since carplates are text
string, a little transformation is needed before insert it to rt-ree table
________________________________
???: Michele Pradella<mailto:michele.pradella at selea.com>
????: ?2016/?2/?19 16:06
???: sqlite-users at mailinglists.sqlite.org<mailto:sqlite-users at
mailinglists.sqlite.org>
??: [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
