----- Original Message -----
From: "Ronan Lucio" <[EMAIL PROTECTED]>
> Here it is a slowly query, it take about 15 seconds:
>
> SELECT f.dtrelease AS fdtrelease, f.cod AS fcod, f.title AS title,
> vf.price AS vfprice, vf.cod AS vfcod
> FROM film AS f, rent_film AS r, film_format AS ff, film_sale AS vf
> WHERE r.codrent = 1123
> AND ff.codfil = f.cod
> AND r.film_format = ff.cod
> AND vf.codrf = l.cod
> AND ff.media = 2
> GROUP BY f.cod
> ORDER BY fdtrelease desc
> LIMIT 0,4
A few hints:
- try an EXPLAIN of this query (simply put EXPLAIN before the SELECT
command) and see if the JOINs give a problem. Check if the right key is
used, what the join-type is how many records MySQL thinks it will return and
if the Extra-info lists problems like temporary table, filesort, etc.
- contrary to what the manual says, it can be faster to move some WHERE
conditions to the JOIN conditions:
FROM a JOIN b ON a.c2=b.c2 JOIN (...) WHERE a.c1=value (...)
can be slower than
FROM a JOIN b ON a.c2=b.c2 AND a.c1=value JOIN (...) WHERE (...)
It seems that the query optimiser does not always make the right choices.
I've noticed that limiting the size of the recordset that is the result of a
JOIN can prevent MySQL from using temporary tables, etc.
- for columns with low cardinality (ff.media might only contain 4 types of
media or so) MySQL sometimes uses an index, while it might be faster to do a
full table scan. Using IGNORE INDEX (index_name) in the join seems to speed
up things. You can also drop such an index if it is not needed for other
purposes.
- try a FORCE INDEX (index_name) to override the index MySQL decides to use
for a JOIN
> Another one:
> SELECT f.cod AS fcod, f.title AS title
> FROM film AS f, rent_film AS r, film_format AS ff
> WHERE r.codloc = 1123
> AND ff.codfil = f.cod
> AND r.film_format = ff.cod
> AND f.type <> 21
> AND ( ( TO_DAYS( CURDATE() ) - TO_DAYS( f.dtrelease ) <= 180
> AND TO_DAYS(CURDATE()) - TO_DAYS(f.dtrelease) >= 0 )
> OR ( r.release = 1 AND f.dtrelease <= CURDATE() ) )
> GROUP BY f.cod
> ORDER BY f.dtrelease DESC
> LIMIT 0,5
The date calculations will most certainly slow things down. Try to make
different expressions with a column name on one side and a constant
expression on the other side. I've had a performance gain of 100 times by
doing this.
AND ( ( TO_DAYS( CURDATE() ) - TO_DAYS( f.dtrelease ) <= 180
will most certainly be slower than
AND ( f.dtrelease > CURDATE() - INTERVAL 180 DAYS)
MySQL has to calculate the first expression for every record in the table,
while it only has to use an index in the second case.
NOTE: although the manual may not agree with the solutions I presented here
, the hints I provided come from my observations of a MySQL 4.0.17 database
with a lot of slow queries. I've eleminated most of the slow queries using
these (and other) techniques.
Regards, Jigal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
