CODEWITHSUNDEEP
mysqllaravel
M Gholami
M Gholami

Reputation: 971

How can I optimize correlated mysql subquery? In laravel

I have 2 tables in Laravel project

1) cars(id, token, name)

2) tracking( id, car_id , lat, lng, point_time)

The tracking table contains all GSP data of the car and contains huge data!

I need to get last tracking data of each car.

SELECT distinct `a`.`id` ,
                ( SELECT MAX(`tracking`.`point_time`)
                FROM `tracking`
                WHERE tracking.car_id = a.id ) as `last_time`
FROM `cars` AS `a`

For now, this query works fine but takes about 4 seconds to execute!

I need a replacement query to run faster.

I prefer to execute a raw sql query to perform faster as you see above.

Upvotes: 1

Views: 218

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

Turn your subquery(O(n^2)) into a join like this:

select c.id,
    t.`last_time`
from `cars` c
left outer join (
    select car_id,
        MAX(t.`point_time`) `last_time`
    from `tracking` t
    group by car_id
    ) t on c.id = t.car_id;

Upvotes: 4

Mil0R3
Mil0R3

Reputation: 3956

I do not think MAX and GROUP BY is that great, just using order by, also you should add index on last_time, try this:

SELECT DISTINCT r.*
FROM 
( 
    SELECT c.id, t.last_time
    FROM cars AS c 
    LEFT JOIN tracking as t
    ON t.car_id = c.id
    ORDER BY c.id, t.last_time DESC
) AS r

Upvotes: 0

Related Questions