Is there a more efficient way of coding this MySQL Query?

Question

I have the following query and it is taking a long time to run and I was wondering is there a more efficient way of coding this. I have two tables DEVICE_LASTUPDATE and DEVICE_EVENTS.

DEVICE_LASTUPDATE table has about 14 records and looks like this:

DEVICE_ID   LAST_UPDATE
device1     2014-06-01 08:25:23
device2     2014-06-01 09:01:45
device3     2014-06-01 07:43:06
device4     2014-06-01 11:13:14
...etc

DEVICE_EVENTS table has over a million records and looks like this:

DEVICE_ID   CREATED_DATE         EVENT_CODE
device1     2014-06-01 08:25:23  1011
device1     2014-06-01 09:01:45  2025
device3     2014-06-01 07:43:06  1015
device2     2014-06-01 15:13:09  2115
device1     2014-06-01 11:20:19  1551
...etc

What I want is for every DEVICE_ID in DEVICE_LASTUPDATE find DEVICE_LASTUPDATE.LAST_UPDATE = DEVICE_EVENTS.CREATED_DATE and return the EVENT_CODE for those DEVICE_ID's

The following SQL is what I running and it works but it is slow (5.5 seconds on average):

SELECT * FROM (SELECT * FROM DEVICE_LASTUPDATE) AS lastupdate 
 JOIN DEVICE_EVENTS
 WHERE DEVICE_EVENTS.CREATED_DATE = lastupdate.LAST_UPDATE
 AND DEVICE_EVENTS.DEVICE_ID = lastupdate.DEVICE_ID

"select all from select all from"... yeah, that's probably quite inefficient. — Niet the Dark Absol
– Niet the Dark Absol, Commented Jun 5, 2014 at 15:58
Am I missing something SELECT * FROM DEVICE_LASTUPDATE AS lastupdate... What's the additional SELECT for? — War10ck
– War10ck, Commented Jun 5, 2014 at 16:00

Thomas Ruiz · Accepted Answer · 2014-06-05 16:00:19Z

4

SELECT LU.DEVICE_ID, E.EVENT_CODE
FROM DEVICE_LASTUPDATE LU
JOIN DEVICE_EVENTS E
  ON (LU.DEVICE_ID = E.DEVICE_ID AND LU.LAST_UPDATE = E.CREATED_DATE);

Plus consider adding an index on LU.DEVICE_ID, LU.LAST_UPDATE and E.DEVICE_ID, E.CREATED_DATE.

answered Jun 5, 2014 at 16:00

Thomas Ruiz

3,6612 gold badges23 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Is there a more efficient way of coding this MySQL Query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related