Re: Slow query

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/