"Mike Martin" <redt...@googlemail.com> wrote in message news:ecc841d80901170714l745078a4uf228f9c8a584f...@mail.gmail.com > The following query runs in around 10 secs with no indexes, however if > I index the start field (datetime) it takes around 10 minutes + > Select guide.id,channels.Channel,start,start,name,stop,stop,cat0 from > guide join channels on pnr=ind left join blacklist on > blacklist.channel =channels.Channel where (start >= > datetime('2009-01-16 02:05:42','-1 hours') or (start < '2009-01-16 > 02:05:42' and stop > '2009-01-16 02:05:42')) and blacklist.channel > is null and start <'2009-01-17 02:05' order by > start,upper(channels.channel) ;
The only clause that the index on start applies to is [start <'2009-01-17 02:05'] (an index cannot be used for an OR condition). I suspect this condition in your case holds for a substantial number of records in guide. An index only helps when it can be used to select a small number of records (a rule of thumb I've seen is 10% or less of the total number in the table). Once it selects too many, it actually hurts performance. You see, a linear scan without the index works in O(N) time (where N is the number of records in the table). A scan using the index works in O(m * log^2(N)) (where m is the number of records satisfying the condition). This only favors the index when m is much smaller than N. The problem is, SQL engine can't know up front how many records will satisfy the condition, and whether the index will be beneficial. If you give it an index, it will use it; if it's a poorly chosen index, the performance will actually suffer rather than improve. In your case, perhaps you could make a reasonable assumption that no program would run for longer than, say, 48 hours, and replace [start <'2009-01-17 02:05'] with [start between '2009-01-15 02:05' and '2009-01-17 02:05'] (choose tighter bounds if possible). Hopefully, only a small portion of records falls in this range. Further, to improve the speed of joins, consider an index on channels.ind and blacklist.channel Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
