Slow MySQL SELECT MAX(ID) with where is not statement

Question

I have got 2 tables.

First table is for my objects (~1,000 rows).

Table Name: object
ID  | name
1   | Dummy object 1
2   | Dummy object 2
3   | Dummy object 3
...
724 | Dummy object 724
...
etc.

Second table is for object events (~200,000 rows)

Table Name: events
ID | ID_OBJ | ID_MES | description  | timestamp
1  | 3      | 3071   | Test event   | 2017-01-28 12:00:01
2  | 4      | 3001   | Doors opened | 2017-01-28 13:00:04
3  | 3      | 3002   | Doors closed | 2017-01-28 13:33:45
...
etc...

When I'm using this query, it takes around 10 seconds to load data from tables:

SELECT
  o.ID, o.name, e.timestamp AS last_event
FROM
  object AS o
LEFT JOIN
  events AS e
ON
  e.ID_OBJ = o.ID
AND
  (SELECT MAX(ID) FROM events WHERE events.ID_OBJ = o.ID AND ID_MES != 3071)
GROUP BY
  o.ID

I need to get list of my objects, including the timestamp from last event, but I don't need include events with ID_MES 3071.

When I remove "AND ID_MES != 3071" from my query, it works really fast. Anyone know a solution for this problem to increase the speed?

Wait, what is the purpose of MAX(ID)? You don't compare it to anything. Couldn't you just write `ON e.ID_OBJ = o.ID AND e.ID_MES != 3071`? — fafl, Jan 28 '17 at 16:36
Check out this answer: http://stackoverflow.com/a/7745635/5710637 — fafl, Jan 28 '17 at 16:40
Sorry, but I can't find that answer useful... :/ By trying other methods, it won't return me object latest event by not including ID_MES 3071... — VigoKrumins, Jan 28 '17 at 17:00
In which case, see http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Jan 28 '17 at 17:12

fafl · Accepted Answer · 2017-01-28T18:45:37.137

1

So this works:

SELECT o.ID, o.name, MAX(e.timestamp) AS last_event
FROM
  object o
LEFT JOIN
  events e
ON
  e.ID_OBJ = o.ID AND e.ID_MES != 3071
GROUP BY
  o.ID

It should be reasonably fast. If you also want to select another field of the most recent event, then you need to look into the greatest-n-per-group tag.

Fiddle: http://rextester.com/XWLWYS80827

edited Jan 28 '17 at 18:45

answered Jan 28 '17 at 18:29

fafl

7,222
3
27
50

Your query works perfectly, but it's not the fastest way to do it. Found a way how to do it faster. Your query response time: (792 total, Query took 0.1146 seconds.). My new query: (792 total, Query took 0.0068 seconds.) Query: SELECT o.ID, o.name e.timestamp AS last_event FROM object AS o LEFT JOIN (SELECT MAX(ID) ID, ID_OBJ FROM events GROUP BY ID_OBJ) AS e ON e.ID_OBJ = obj.ID LEFT JOIN events ON events.ID = e.ID AND events.ID_MES != 3071 GROUP BY obj.ID; But thanks anyways, mate! :) – VigoKrumins Jan 28 '17 at 18:46
It's cool that you found a faster query, but somehow it doesn't compile for me. – fafl Jan 28 '17 at 18:54
Fixed my query: SELECT o.ID, o.name, events.timestamp AS last_event FROM object AS o LEFT JOIN (SELECT MAX(ID) ID, ID_OBJ FROM events GROUP BY ID_OBJ) AS e ON e.ID_OBJ = o.ID LEFT JOIN events ON events.ID = e.ID AND events.ID_MES != 3071 GROUP BY o.ID; – VigoKrumins Jan 28 '17 at 18:55
I get different results from this query, it doesn't seem to find older events with ID_MES != 3071 – fafl Jan 28 '17 at 19:05
How fast is this one? `SELECT o.ID, o.name, events.timestamp AS last_event FROM object AS o LEFT JOIN (SELECT MAX(ID) ID, ID_OBJ FROM events WHERE ID_MES != 3071 GROUP BY ID_OBJ) AS e ON e.ID_OBJ = o.ID LEFT JOIN events ON events.ID = e.ID GROUP BY o.ID;` – fafl Jan 28 '17 at 19:10
Page loads in 12,78sec by using your last query, but when using my query page loads in 2,38sec. Both queries return same data. – VigoKrumins Jan 28 '17 at 19:20
By carefully looking through the data, I see the difference in the timestamps... In my query, if last event is ID_MES = 3071, then it returns timestamp as NULL. I don't know how to fix it without losing the query speed. In my case, I'm okay with this kind of result too, but for further use it'd be useful to return data as in your query. – VigoKrumins Jan 28 '17 at 19:34

Slow MySQL SELECT MAX(ID) with where is not statement

1 Answers1