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).
-- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
