Optimizing Query in mysql

Question

i've been scratching my head over the past hours to find an optimization for a query which seems to cost a lot of time:

SELECT d.deviceID ,e.latitude, e.longitude,MAX(e.timestamp) as timestamp 
FROM Device as d INNER JOIN EventData as e ON d.deviceID=e.deviceID 
WHERE e.accountID='$account' AND e.timestamp<=$time AND (e.deviceID IN (SELECT deviceID FROM DeviceList WHERE accountID='$account')) 
GROUP BY (e.deviceID);

so i'm querying a set of devices and for each device the last associated event.
Note that $account/$time are variables obtained from a $_POST.
EDIT : Tables structure :

CREATE TABLE IF NOT EXISTS `Device` (
      `deviceID` varchar(32) NOT NULL,
      `equipmentType` varchar(40) DEFAULT NULL,
      `description` varchar(128) CHARACTER SET utf8 DEFAULT NULL

);
CREATE TABLE IF NOT EXISTS `DeviceList` (
  `accountID` varchar(32) NOT NULL,
  `groupID` varchar(32) NOT NULL,
  `deviceID` varchar(32) NOT NULL,
);

CREATE TABLE IF NOT EXISTS `EventData` (
  `accountID` varchar(32) NOT NULL,
  `deviceID` varchar(32) NOT NULL,
  `timestamp` int(10) unsigned NOT NULL,
  `statusCode` int(10) unsigned NOT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `gpsAge` int(10) unsigned DEFAULT NULL,
  `speedKPH` double DEFAULT NULL
);

@OliverQueen i ran it and it shows 3 rows 0: {0: "1", 1: "PRIMARY", 2: "d", 3: "ALL", 4: null, 5: null, 6: null, 7: null, 8: "498",…} 1: {0: "1", 1: "PRIMARY", 2: "e", 3: "ref", 4: "PRIMARY", 5: "PRIMARY", 6: "68",…} 2: {0: "2", 1: "DEPENDENT SUBQUERY", 2: "DeviceList", 3: "ref", 4: "PRIMARY", 5: "PRIMARY" — abdessamad el ouardi
– abdessamad el ouardi, Commented Feb 9, 2016 at 23:19

Mr. Llama · Accepted Answer · 2016-02-09 22:20:16Z

2

You don't need the Device table at all. Also, an IN [subquery] can be avoided with a simple JOIN:

SELECT
  e.eviceID,
  e.atitude,
  e.ongitude,
  MAX(e.timestamp) AS timestamp
FROM EventData e
JOIN DeviceList d
  ON e.deviceID = d.deviceID
WHERE e.deviceID   = :account
  AND e.timestamp <= :time
GROUP BY deviceID;

Also, you should be using prepared statements instead of putting user-controlled values into a query, but that's a separate topic.

edited Feb 9, 2016 at 22:20

answered Feb 9, 2016 at 22:18

Mr. Llama

21k3 gold badges70 silver badges121 bronze badges

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

6 Comments

random_user_name Over a year ago

How does that include / factor in this part of the query: AND (e.deviceID IN (SELECT deviceID FROM DeviceList WHERE accountID='$account')) GROUP BY (e.deviceID);?

Mr. Llama Over a year ago

@cale_b - Fair enough. I assumed that DeviceList and EventData both had the same deviceID values, but that may not be the case. Either way, the Devices table isn't actually providing value here.

abdessamad el ouardi Over a year ago

Thanks for the reply @mr-llama , but i will need the Device table because of some informations stored on it. i'm guessing the problem is in that the query gets all rows of EventData then apply the MAX aggregate, is there any way of getting the last Event of each device without getting all the rows (Note that EventData has a huge amount of data for each device)

Mr. Llama Over a year ago

@abdessamadelouardi - What is your query getting from Device that it's not already getting from DeviceList? The only column I see you fetch from it is deviceID which you could get from DeviceList instead.

abdessamad el ouardi Over a year ago

@Mr.Llama i'm getting the device description, and from the DeviceList i need to get all the devices for each groupID

|

Collectives™ on Stack Overflow

Optimizing Query in mysql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related