0

See Original question

I'm fetching the activity data for the latest activity for each company as part of a bigger query. I've been using the method provided by ypercube, and it works fine, but I find it too slow on larger results.

SELECT
d.*,
a.*
FROM difi d
LEFT JOIN (
  SELECT orgnr,
  MAX(resolved) as maxdate
  FROM activities
  WHERE resolved IS NOT NULL
  AND deleted IS NULL
  GROUP BY orgnr
) as x ON x.orgnr=d.orgnr

LEFT JOIN activities a ON a.deleted IS NULL
  AND a.orgnr=x.orgnr
  AND a.resolved=x.maxdate

This performs at 1.3 seconds with a LIMIT 500, and 2.4 seconds with a LIMIT 1000. Can this query be optimized to run with thousands of results, faster?

I figure the bottleneck is the subquery, as it is running for each company(?).

Here's an explain with LIMIT 1000:

+----+-------------+------------+------+------------------------+---------+---------+---------+---------+----------------------------------------------+
| id | select_type | table      | type | possible_keys          | key     | key_len | ref     | rows    | Extra                                        |
+----+-------------+------------+------+------------------------+---------+---------+---------+---------+----------------------------------------------+
|  1 | PRIMARY     | d          | ALL  | NULL                   | NULL    | NULL    | NULL    | 1001146 |                                              |
|  1 | PRIMARY     | <derived2> | ALL  | NULL                   | NULL    | NULL    | NULL    |   33241 |                                              |
|  1 | PRIMARY     | a          | ref  | orgnr,resolved,deleted | orgnr   | 4       | x.orgnr |       1 |                                              |
|  2 | DERIVED     | activities | ref  | resolved,deleted       | deleted | 9       |         |   33166 | Using where; Using temporary; Using filesort |
+----+-------------+------------+------+------------------------+---------+---------+---------+---------+----------------------------------------------+
4 rows in set (0.30 sec)

Using MySQL 5.5.16

Update

I've been at this all weekend and I think I've found the solution (method from tosin here)

SELECT
*
FROM difi d

LEFT JOIN activities a ON a.id=
    (SELECT ax.id
     FROM activities ax
     WHERE ax.orgnr=d.orgnr
     AND ax.deleted IS NULL
     AND ax.resolved IS NOT NULL
     ORDER BY ax.resolved DESC
     LIMIT 1)

As far as I can tell, this gets me the latest activity for each company. And it performs at 1,001,146 results in 0.0025 sec. Any caveat on this?

  • I doubt it,at beginning you are selecting the whole difi table which has the highest cost,so an index wont help. – Mihai May 4 '14 at 11:49
  • @stiq: Yes, this is another way of solving the problem, and is usually quite efficient, especially if you have a small number of rows from the "base" (difi here) table that you want to show in the results. – ypercubeᵀᴹ May 5 '14 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.