Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My problem seems like it should have a much simpler solution than what I have come up with. Starting with this data set:

log_table

+--------+-----------+------------------+---------+
| log_id | entity_id |       date       | comment |
+--------+-----------+------------------+---------+
|      1 | A         | 2012-10-23 07:50 | foo     |
|      2 | B         | 2012-10-23 07:59 | bar     |
|      3 | B         | 2012-10-23 08:11 | baz     |
|      4 | A         | 2012-10-23 08:23 | bat     |
+--------+-----------+------------------+---------+

Say I wanted to get the latest date of log entries for each entity so that the result looked like:

Results:
+-----------+------------------+--------------+
| entity_id |  last_log_date   | last_comment |
+-----------+------------------+--------------+
| B         | 2012-10-23 08:11 | baz          |
| A         | 2012-10-23 08:23 | bat          |
+-----------+------------------+--------------+

I'm currently using MySQL that looks something like:

SELECT
  `entity_id`,
  `date` AS last_log_date,
  `comment` AS last_comment
FROM (
  SELECT *
  FROM `log_table`
  ORDER BY `date` DESC, log_id ASC
) AS `ordered_log`
GROUP BY `entity_id`

This works fine but it doesn't seem very efficient to me, there has to be a better way of doing this, surely?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Create a Subquery that Gathers Keys from the the log_table with Maximum Date Per Entity. Then, perform an INNER JOIN of that Subquery back to the log_table.

SELECT
    B.entity_id,B.last_log_date,B.last_comment
FROM
(
    SELECT entity_id,MAX(last_log_date) last_log_date
    FROM log_table GROUP BY entity_id
) A INNER JOIN B USING (entity_id,last_log_date);

Give it a Try !!!

You can really speed this up if you have a compound index like this

ALTER TABLE log_table ADD INDEX entity_date_ndx (entity_id,last_log_date);

Indexes on each separate column may yield index merges. This compound index will bypass that.

Please try LEFT JOIN instead of INNER JOIN

SELECT
    B.entity_id,B.last_log_date,B.last_comment
FROM
(
    SELECT entity_id,MAX(last_log_date) last_log_date
    FROM log_table GROUP BY entity_id
) A LEFT JOIN B USING (entity_id,last_log_date);
share|improve this answer
    
Ok this query seems to be more efficient. On a table with 87,283 distinct entity_id and 1,309,252 records, my query takes too long (more than 50s until I killed the query) but your query takes just over 11 seconds. Is there a way to speed this up further? I have indexes on the entity_id and the date columns already. Ideally the query should run more or less instantly if possible... –  Asgrim Oct 23 '12 at 15:13
    
@Asgrim Do you have two separate indexes on the entity_id and date columns, or one index across both columns? –  matts Oct 23 '12 at 16:10
    
@matts As per @RolandoMySQLDBA's edited answer I added a compound index to the columns and this didn't make any difference. Running just the subquery itself (i.e. the SELECT entity_id,MAX(last_log_date) last_log_date FROM log_table GROUP BY entity_id part) and that is what is taking the time to run (it still takes 11 seconds). It seems to me that this isn't going to get any quicker? –  Asgrim Oct 23 '12 at 16:17
1  
Please change the INNER JOIN to LEFT JOIN to see it ordering of subquery is preserved and faster. –  RolandoMySQLDBA Oct 23 '12 at 16:25
    
@RolandoMySQLDBA That does improve it slightly, but running only the subquery (i.e. SELECT entity_id, MAX(`date`) last_log_date FROM log_table GROUP BY entity_id) is taking 8 seconds on its own. What I asked in my last comment is if there is a way to vastly increase the performance of just that query - that would most likely solve all the issues... –  Asgrim Oct 24 '12 at 7:39

The subquery works; here's how you would do it without a subquery:

SELECT
  `entity_id`,
  SUBSTRING_INDEX(GROUP_CONCAT(`date` ORDER BY `date` DESC), ',', 1) AS last_log_date,
  SUBSTRING_INDEX(GROUP_CONCAT(`comment` ORDER BY `date` DESC), ',', 1) AS last_comment
FROM `log_table`
GROUP BY `entity_id`

The query above uses GROUP_CONCAT to generate a long concatenation of values per group, which is then parsed to extract first token via SUBSTRING_INDEX.

You could have an excellent way of solving it if only MySQL supported Window Functions (aka Analytic Functions). It does, not , and we are left with hacks around GROUP_CONCAT.

share|improve this answer
    
Thanks - a different answer that also worked, but was just as slow as the other.. it seems a bit hackier too, but nice thinking :) –  Asgrim Oct 25 '12 at 16:33
    
What keys do you have on that table? a KEY(entity_id, date) should do well for my query. –  Shlomi Noach Oct 26 '12 at 10:24
    
As I described in the comments in @RolandoMySQLDBA's answer, adding a compound key didn't make any difference. The issue in the end was the huge amount of data in the TEXT field in the comment column meaning there was too much disk seeking going on. I preferred the subquery to using a GROUP_CONCAT hack, that's all. –  Asgrim Oct 26 '12 at 19:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.