MySQL sorting performance problem

Question

I have a query with subquery but subquery table has approximately 500.000 records and runing this query takes too long. How can I speed up this query? Any suggestions:

SELECT ID, VehicleID, Plate, VehicleType, 
    COALESCE(
        (SELECT EngineState 
        FROM Locations 
        WHERE Locations.VehicleID = Clients.VehicleID ORDER BY ID DESC LIMIT 1), 
        0
    ) EngineState 
FROM Clients 
WHERE ID IN (SELECT ClientID FROM UserClients WHERE UserID=@UserID);

There are 3 more columns which query last record from Locations table:

COALESCE(
    (SELECT EngineState FROM Locations 
    WHERE Locations.VehicleID = Clients.VehicleID ORDER BY ID DESC LIMIT 1), 
    0
) EngineState

As I see sorting the results in Locations table is the performance factor there. Locations table if filled every minute by 1000 vehicles' location data. x, y, speed ...

Answer 1

Create the following indexes:

Locations (vehicleId, id)
UserClients (UserID, ClientID)

and rewrite your query a little:

SELECT  ID, VehicleID, Plate, VehicleType, l.*
FROM    Clients
LEFT JOIN
        Locations l
ON      l.id =
        (
        SELECT  id
        FROM    Locations li
        WHERE   li.VehicleID = Clients.VehicleID
        ORDER BY
                li.VehicleID DESC, li.id DESC
        LIMIT 1
        )
WHERE   Clients.ID IN
        (
        SELECT  ClientID
        FROM    UserClients
        WHERE   UserID = @UserID
        )

Since your VehicleID is a VARCHAR(64), you should make sure that Clients.VehicleID and Locations.VehicleID use the same character set and same collation.

Answer 2

Indexes are needed on all columns used in JOINs plus UserID in UserClient

Queries made on the Select are slowing down execution time a lot.
The following query would improve performance.

SELECT c.ID, c.VehicleID, c.Plate, c.VehicleType, 
IFNULL(l.enginestate, 0) AS enginestate
FROM Clients c
JOIN (
    SELECT MAX(ID) AS ID, vehicleID
    FROM Locations 
    GROUP BY vehicleID
) ll
    ON ll.VehicleID = c.VehicleID 
JOIN Locations l
    ON ll.ID = l.ID
JOIN UserClients uc
    ON uc.clientID = c.ID 
WHERE uc.UserID=@UserID

Also I prefer IFNULL to COALESCE when getting only one field's value.