On 9/14/2011 2:07 PM, Jan Hudec wrote:
On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote:
select geo.id, min_age, max_age, age_bottom, age_top, name, color
>from geo left join intervals i on i.id = (
select id from intervals
where age_bottom>=
(select age_bottom from intervals where name = geo.max_age)
and age_top<=
(select age_top from intervals where name = geo.min_age)
order by (age_bottom - age_top) limit 1
);
Looking at the query I'd say the needed indices are:
create index intervals_name on intervals (name)
create index intervals_ages on intervals (age_bottom, age_top)
separate indices on age_bottom and age_top are much less useful to this
query.
I don't believe an index on intervals(age_bottom, age_top) can be used
here. Or rather, it can be, but really only one half of it, to satisfy
age_bottom>=X condition. The other half, age_top<=Y, needs to be
satisfied with a linear scan. That's why I said that an index on
intervals(age_bottom) would help, or one on intervals(age_top), but not
both at the same time.
Think about it this way. You have a phone book, where names are sorted
by last name, then first name. You want to find all people whose last
name is greater than 'Smith' and first name less than 'John'. The
alphabetic order helps you with the first half, but not really with the
second half - names satisfying both conditions don't appear sequentially
in the list.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
