MySQL sorting performance problem

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 co开发者_StackOverflowlumns 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 ...

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.

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.