René Fournier wrote:
SELECT MAX(id) FROM history
WHERE account_id = 216 GROUP BY asset_id
...works. Returns, e.g.: 1234, 3456, 5483, 8382.
SELECT history.* FROM history
WHERE history.id IN (1234, 3456, 5483, 8382 )
...works too. But if I try to combine them using a subquery, a la...
SELECT history.* FROM history
WHERE history.id IN ( SELECT MAX(id) FROM history WHERE account_id =
216 GROUP BY asset_id )
...it pretty much hangs MySQL. CPU goes to 100%, ten minutes later, I
have to kill the connection. I can't figure out why. Any ideas? Any
suggestions?
...Rene
Put EXPLAIN in front of your query to see what mysql does
<http://dev.mysql.com/doc/refman/4.1/en/explain.html>. You will almost
certainly see that the subquery's select_type is "DEPENDENT SUBQUERY", which
means that the subquery is run for each row of the outer query, rather than just
once! Unfortunately, mysql's optimizer is not so good at subqueries yet.
The efficient way to do this is to store the results of the first query (the
subquery) in a temporary table, then join to it to get the right rows. Assuming
id is the primary key of table history, the following should do:
CREATE TEMPORARY TABLE groupmax (id INT UNSIGNED);
LOCK TABLES history READ;
INSERT INTO groupmax
SELECT MAX(id) FROM history WHERE account_id = 216 GROUP BY asset_id;
SELECT history.*
FROM history
JOIN groupmax ON history.id = groupmax.id
UNLOCK TABLES;
DROP TABLE groupmax;
(Adapted from the example in the manual
<http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html>)
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
