Thanks for that Is it possible to do an index on a substring of a field eg: subtr(start,10) to get just the day
On 17/01/2009, Igor Tandetnik <[email protected]> wrote: > "Mike Martin" <[email protected]> wrote > in message > news:[email protected] >> 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 > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
