Strange case in 3.2.7 , why Index create on multiple column isn't faster than index created on Single column

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

Strange case in 3.2.7 , why Index create on multiple column isn't faster than index created on Single column

Bo Lin
 I have a Table with about 800,000 record
DB Version : 3. 2. 7

The Sql looks like
    select x, y, sum(z)/1000 as bw from aa where        
        a=1 and b=1 and
       c =1 and d= 6 group by x, y having count(*) > 1 order by         bw desc
       limit 10

Column "d" is all set to 6
1/3 of total  record is (a=1,b=1,c=1)
1/3 of total  record is (a=2,b=2,c=2)
1/3 of total  record is (a=3,b=3,c=3)
Column x,y may have random value ?

I just test the above Sql under different index (each time, I just create one
index and drop another)
1)  create index 1 on  aa (d)
2)  create index 2 on  aa (a,b,c,d)

Expect :
    I think Using second index should be much faster than using first index.
Actually
    That two index almost give the same performance. both of them will take
about 15 seconds

Why?  thanks in advice  :)

__________________________________________________
赶快注册雅虎超大容量免费邮箱?
http://cn.mail.yahoo.com