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
