Question about Index using

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Question about Index using

Bo Lin
 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