Hi there.
I've got a database (the schema is:
http://compsoc.man.ac.uk/~cez/2004/01/14/tv-schema.sql) for television
data. Now, one of the things I want to use this for is a now and next
display. (much like http://teletext.com/tvplus/nownext.asp ).
I've got a view defined like this:
CREATE VIEW progtit AS SELECT programme.*, title_seen, title_wanted, title_text FROM (programme NATURAL JOIN title);
And to select the programmes that are on currently and next, I'm doing
something like this:
SELECT *
FROM progtit AS p1 LEFT JOIN progtit AS p2 ON p1.prog_next = p2.prog_id
WHERE prog_start <= '2004-01-14 23:09:11'
AND prog_stop > '2004-01-14 23:09:11';
Now, unfourtunately this runs rather slowly (takes around 1sec to
complete on my machine), as it (AFAIK) ends up building a complete
instance of the progtit view and then joining the current programmes
with that, instead of just finding the current set of programs and then
selecting the relevant rows from the view.
Now, I know I could just two it in two seperate passes for the current
programmes and those after them, but I'd be neater to do it in one.
So, is there any way to optimize the above query? Any clues, or
references would be wonderful.
Thanks.
--
Ceri Storey <cez@necrofish.org.uk>