On Tue, 2005-10-11 at 13:48 -0400, Jason Tackaberry wrote: > Probably not a bad idea to do prefetches like that. There's a pretty > high initial overhead, so it's better to get more rows than you need. > For example, querying for the next 2 hours of program data takes 0.1 > seconds and returns 200 rows. Querying for the next 12 hours returns > 2000 rows and takes 0.2 seconds. 10X the data for only 2X extra > execution time. Actually, now that I think about that, something > doesn't seem right there. Smells like an index isn't getting used (or > doesn't exist).
Indeed. I have an index on (start,stop) in the programs table. I copied the existing kaa.epg logic without really thinking about it, and for searching within a time range, it does "WHERE start <= $stop AND stop >= $start" The problem is that this can't really make very good use of the index that exists on start. The db must scan all start <= $stop, which could be a large list. Ideally what we'd like to do is specify a range for start. We know that start must be before $stop, but we don't know what value it must be after. Well, if when we load the database we do a prepass and figure out the maximum program length, we could do "WHERE start <= $stop AND start >= $start-$max_program_len AND stop >= $start". With this little tweak, kaa.epg2 returns 12 hours of program data in 0.125 seconds, and kaa.epg takes 0.170 seconds. This was admittedly a bit of a digression. :) Jason.
signature.asc
Description: This is a digitally signed message part
