Slow query

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;

Showing rows 0 - 3 (4 total, Query took 2.4823 sec)

Explain SQL returns this;

id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
SIMPLE
mi
ALL
NULL
NULL
NULL
NULL
104
Using
where;
Using
temporary; Using filesort
1
SIMPLE
s
ALL
NULL
NULL
NULL
NULL
3690
Using
where
1
SIMPLE
m
ALL
NULL
NULL
NULL
NULL
298


Thanks

Steven


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

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/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Slow query

------=_Part_92022_11018370.1216817995241
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Wed, Jul 23, 2008 at 4:48 AM, Chris 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

------=_Part_92022_11018370.1216817995241--