GROUP BY having MAX date

mysql sql optimization greatest-n-per-group
J-J picture J-J · Aug 14, 2013 · Viewed 152.2k times · Source

I have problem when executing this code:

SELECT * FROM tblpm n 
WHERE date_updated=(SELECT MAX(date_updated) 
FROM tblpm GROUP BY control_number 
HAVING control_number=n.control_number)

Basically, I want to return the most recent date for each control number. The query above returns correct output but it takes 37secs. before the output was shown.

Is there any other sql clause or command that can execute faster than the query above?

Thanks in advance.

Answer

Bill Karwin picture Bill Karwin · Aug 14, 2013

Putting the subquery in the WHERE clause and restricting it to n.control_number means it runs the subquery many times. This is called a correlated subquery, and it's often a performance killer.

It's better to run the subquery once, in the FROM clause, to get the max date per control number.

SELECT n.* 
FROM tblpm n 
INNER JOIN (
  SELECT control_number, MAX(date_updated) AS date_updated
  FROM tblpm GROUP BY control_number
) AS max USING (control_number, date_updated);

Related questions

delete all from table

what's faster? DELETE FROM table_name; or DELETE FROM table_name where 1=1; why? does truncate table work in access?

Read More
Storing JSON in database vs. having a new column for each key

I am implementing the following model for storing user related data in my table - I have 2 columns - uid (primary key) and a meta column which stores other data about the user in JSON format. uid | meta -------------------------------------------------- 1 | {name:[…

Read More
MYSQL OR vs IN performance

I am wondering if there is any difference in regards to performance between the following SELECT ... FROM ... WHERE someFIELD IN(1,2,3,4) SELECT ... FROM ... WHERE someFIELD between 0 AND 5 SELECT ... FROM ... WHERE someFIELD = 1 OR someFIELD = 2 OR someFIELD = 3 ... or will MySQL optimize the SQL …

Read More