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