CODEWITHSUNDEEP
mysql
seaBass
seaBass

Reputation: 597

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

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

Upvotes: 0

Views: 35

Answers (1)

Thomas Ruiz
Thomas Ruiz

Reputation: 3661

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.

Upvotes: 4

Related Questions