Hi Igor 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 :) Igor Tandetnik <[EMAIL PROTECTED]> 写道: Bo Lin wrote: > 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" > > how can I use the second index, can sqlite can support "select" to > specify certain index ? I suspect "or" confuses the optimizer. SQLite's query planner is not as sofisticated as that of some DBMS's (that are usually much larger and infinitely more expensive). I haven't tried it, but I strongly suspect if you restate your query as shown below, it would use the index you want: select * from test where a=0 and b=1 union all select * from test where a=1 and b=1 Another possibility along the same lines is select * from (select 0 a union all select 1 a) ids join test on (test.a = ids.a and test.b = 1) Also, running ANALYZE statement may help. It gathers statistics about the value distribution in your indexes, which the query planner presumably considers. I haven't personally seen it help much, but perhaps in your situation with very different distributions it would. Igor Tandetnik __________________________________________________ 赶快注册雅虎超大容量免费邮箱? http://cn.mail.yahoo.com