CREATE TABLE test (
a tinyint, /*range 0-3*/
b tinyint, /*range 0-3*/
c tinyint, /*range 0-3*/
d tinyint, /*range 0-3*/
CREATE INDEX idx_trafficlog_a on test (a);
CREATE INDEX idx_trafficlog_abcd on test (a,b,c,d);
According to our requirement, we may have TWO type Sql
1)select * from test where a=1
2)select * from test where a=1 and b in (0,1) and c=1 and d=2
select * from test where a in (0,1) and b in (0,1) and c=1 and d=2
select * from test where a in (0,1) and b in (0,1) and c in (1,2) and d=2
After I "explain" the Sql string, I found both TWO type Sql use index
"idx_trafficlog_a", But I wish the second type Sql can use Index
I have 2 question
1) How can I use Index "idx_trafficlog_abcd"? And under the second Sql type.
if using this index is much faster than using index "idx_trafficlog_a" ?
2) Maybe someone suggest that juse create one index "idx_trafficlog_abcd"
But I test some sql like "select a,b,c,count(*) from test where a=1 group by
b,c" under index "idx_trafficlog_a" OR index "idx_trafficlog_abcd".
Using first index is faster that using the seconds index. So I just create two
index , wish them to work for different Sql. Is it reasonable ?