Here is a table and with 2 index creatrf on it .
==================================================
CREATE TABLE test (
a tinyint, /*range 0-3*/
b tinyint, /*range 0-3*/
c tinyint, /*range 0-3*/
d tinyint, /*range 0-3*/
e tinyint,
f tinyint,
g tinyint,
h tinyint,
i bigint,
);
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
or
select * from test where a in (0,1) and b in (0,1) and c=1 and d=2
or
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
"idx_trafficlog_abcd"
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 ?
Any suggestion ? thanks first !!!!!
__________________________________________________
赶快注册雅虎超大容量免费邮箱?
http://cn.mail.yahoo.com
