Hi Drh I just found a strange case , can you give me some explaination ? 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) 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 :)
[EMAIL PROTECTED] 写道: Bo Lin wrote: > Hi , > > Here is a sql string ,like : select * from test where (a=0 or a=1) and b=1 ; > and column a range from 1-10000, and column b range from 0-1. and DB has > about 300,000 record with colum a and b configured randomly . > > Two index is create on "test" table . First is on "column b" and the second > is on "a,b" > > but when I try to use "explain" , I found the first index is used. but > obviousely if sqlite can use the second index , the performance can be > improved a lot . > > how can I use the second index, can sqlite can support "select" to specify > certain index ? > SQLite will only use one index at a time. So create your index like this: CREATE INDEX idx ON test(b,a); It *should* also work to create the index this way: CREATE INDEX idx ON test(a,b); But I just tried this and there appears to be a bug in the optimizer that is preventing it from working properly. I'll look into it. -- D. Richard Hipp __________________________________________________ 赶快注册雅虎超大容量免费邮箱? http://cn.mail.yahoo.com