On 4-Feb-06, at 5:36 PM, Michael Stassen wrote:
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.
Yes, this was what I was thinking later—that it's running the
subquery on each row (~60,000 of them), which would explain the CPU
load. Oh, and to answer another question, I am on 5.0.16.
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;
Thanks! I will try this Monday. On a related note, does the
'temporary table' approach (creating, locking, dropping)
significantly slow down queries? I haven't had to use them yet, and
my application is somewhat performance sensitive, so... just curious.
...Rene
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
