0

I am having trouble debugging a slow query, when I take it appart they perform relatively fast, let me break it down for you:

The first query, which is my subquery, groups all rows by lmu_id (currently only 2 unique ones) and returns the max(id) in other words the last inserted row.

SELECT max(id) FROM `position` GROUP by lmu_id  
-> 15055,15091

2 total, Query took 0.0030 seconds

The outer query retrieves the full row of those two positions, so here I've manually inserted the ids (15055,15091)

SELECT * FROM `position` WHERE id IN (15055,15091)

2 total, Query took 0.1169 sec

Not the fastest query, but still a bink of an eye.

Now my problem is I do not understand why if I combine these two queries the whole system crashes:

SELECT * FROM `position` AS p1 WHERE p1.id IN (SELECT max(id) FROM `position` AS p2 GROUP by p2.lmu_id)

takes forever, 100% cpu, crashing, lost patience after 2 minutes, service mysql restart

For your reference I did an explain of the query

EXPLAIN SELECT * FROM `position` AS p1 WHERE p1.id IN (SELECT max(p2.id) FROM `position` AS p2 GROUP by p2.lmu_id)
id  select_type         table   type    possible_keys   key                     key_len     ref     rows    Extra
1   PRIMARY             p1      ALL     NULL            NULL                    NULL        NULL    7613    Using where
2   DEPENDENT SUBQUERY  p2      index   NULL            position_lmu_id_index   5           NULL    1268    Using index

id is the primary key, and lmu_id is a foreign key and also indexed. I'm really stumped. Why is the final query taking so long/crashing? What other things shoud I look in to?

Moak
  • 12,596
  • 27
  • 111
  • 166

2 Answers2

2

Joins can work too.

SELECT *
FROM `position` AS p1
    INNER JOIN (SELECT max(id) FROM `position` GROUP by lmu_id) p2 on (p1.id = p2.id)

Scott's answer is good too, as I find EXISTS tends to run quite fast as well. In general, avoid IN.

Also try

SELECT *
FROM `position` AS p1
GROUP BY p1.lmu_id
HAVING p1.id = (SELECT max(id) FROM `position` where lmu_id = p1.lmu_id)
slevin
  • 308
  • 1
  • 8
1

I've found that using EXISTS runs much faster than IN sub selects.

http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

Subqueries with EXISTS vs IN - MySQL

Community
  • 1
  • 1
Scott Saunders
  • 29,840
  • 14
  • 57
  • 64
  • how would he use `EXISTS` in his context? He is trying to 'join' the other table on a value that comes from the sub query. I don't think `EXISTS` will help here. – nl-x May 20 '14 at 21:28
  • I was not able to figure out how to use `EXISTS` to behave as I expected, like nl-x mentioned – Moak May 20 '14 at 21:34