On Mon, Sep 12, 2011 at 12:16:55 -0400, Igor Tandetnik wrote:
> On 9/12/2011 12:02 PM, Mr. Puneet Kishor wrote:
> >
> >On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote:
> >>Something like this:
> >>
> >>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
> >>);
> >>
> >
> >
> >Thanks Igor. The above does work and produces the correct result. The
> >query speed, however, is pretty slow ~ 75 seconds. So, I created
> >indexes on intervals.name, geo.max_age, and geo.min_age, and that
> >brought the query time to ~ 11 seconds. Still too slow.
>
> Indexes on geo.max_age and min_age are unlikely to help with this
> query (use EXPLAIN QUERY PLAN to see which indexes are actually
> used). An index on intervals.age_bottom might. So would an index on
> intervals.age_top (but not both at the same time).
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.
--
Jan 'Bulb' Hudec <b...@ucw.cz>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
