Re: Join optimisation Quandry

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.