On Wed, Jul 23, 2008 at 4:48 AM, Chris <dmagick@xxxxxxxxx> wrote: > Steven Macintyre wrote: > > Hi all, > > > > Below is the query we are getting a slow response on ... 2seconds ... > > with 4 records we were hoping to get it much quicker. > > > > Can someone advise if i can optimise this? > > > > SELECT s.movie_id, m.movie_name, movie_shortname, image_name, > > image_extension, m.trailerAvail > > FROM sessions AS s > > LEFT JOIN movies_information AS mi ON mi.movie_id = s.movie_id > > LEFT JOIN movies AS m ON m.movie_id = s.movie_id > > WHERE DATE( date_time ) <= ( DATE_ADD( NOW( ) , INTERVAL 21 > > DAY ) ) > > AND image_name != '' > > GROUP BY s.movie_id > > ORDER BY RAND( ) > > LIMIT 4; > > Do you have indexes on: > > movies_information(movie_id); > movies(movie_id); > sessions(movie_id); > > How many rows in each table? > > Even though you're only selecting 4 rows you are doing an order by > rand() which has to look at *all* rows of the result set to randomize > the results - AND you are grouping by the movie_id - both of which can > kill performance. > > Why are they left joins? can a movie not have information, or can it not > have a session? > > Left joins have to inspect all rows in each of the tables as well. > > Try something like this: > > select > m.movie_id, > m.movie_name, > m.movie_shortname, > from > movies m > inner join > movies_information mi on (m.movie_id=mi.movie_id) > where > m.movie_id in > ( > select movie_id from sessions where date(date_time) <= > (date_add(now(), interval 21 day)) > order by rand() > limit 4 > ); > > Only the sessions table needs to check the date and do the random ordering. > > Once you have 4 movie_id's to choose from, the rest should be pretty quick. > > -- > Postgresql & php tutorials > http://www.designmagick.com/ > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > also try using EXPLAIN on the SQL to see how the optimizer is attempting to retrieve the data -- Bastien Cat, the other other white meat
