On Wed, 14 Jan 2004, Ceri Storey wrote: > 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.
As a starting point, we're likely to need the exact query, explain analyze output for the query and version information. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
