CODEWITHSUNDEEP
mysql
Gulmuhammad Akbari
Gulmuhammad Akbari

Reputation: 2036

Mysql Nested Query Speed Is Very Slow

I have a table by the name of vehicle_plate as follow:

enter image description here

And the above table total records is 715210 and I want to count the vehicles by latest or updated plate type in specific zone and province but the when I run the below query, The query speed is very slow. I mean I never get the result its just loading.

            SELECT `pt`.`pt_dr` AS
            TYPE , COUNT( DISTINCT vp.vehicle_v_id ) AS vehicles, `p`.`province_dr` AS province_name, `p`.`id` AS id, vp.vehicle_v_id
            FROM (
            `vehicle_plate` AS vp
            )
            LEFT JOIN provinces AS p ON p.id = vp.provinces_province_id
            LEFT JOIN plate_types AS pt ON pt.id = vp.plate_types_pt_id
            WHERE 1
            AND p.zone_zone_id = '1'
            AND p.id = '1'

            AND vp.id
            IN (

            SELECT vp1.id
            FROM vehicle_plate AS vp1
            GROUP BY vp1.vehicle_v_id
            )
            GROUP BY vp.vehicle_v_id, `vp`.`plate_types_pt_id`
            ORDER BY `p`.`zone_zone_id` , `vp`.`provinces_province_id` , `vehicles` DESC

Note: One vehicle may has more than one plate and I want to count the updated or latest plate type.

Sample Data: enter image description here

Upvotes: 1

Views: 45

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

Sometime a inner join is more performant that the in clause 

      SELECT `pt`.`pt_dr` AS
        TYPE , COUNT( DISTINCT vp.vehicle_v_id ) AS vehicles, `p`.`province_dr` AS province_name, `p`.`id` AS id, vp.vehicle_v_id
        FROM (
        `vehicle_plate` AS vp
        )
        LEFT JOIN provinces AS p ON p.id = vp.provinces_province_id
        LEFT JOIN plate_types AS pt ON pt.id = vp.plate_types_pt_id
        INNER JOIN   (

        SELECT vp1.id as id
        FROM vehicle_plate AS vp1
        GROUP BY vp1.vehicle_v_id
        ) t on vp.id  = t.id 
        WHERE 1
        AND p.zone_zone_id = '1'
        AND p.id = '1'

        GROUP BY vp.vehicle_v_id, `vp`.`plate_types_pt_id`
        ORDER BY `p`.`zone_zone_id` , `vp`.`provinces_province_id` , `vehicles` DESC

Upvotes: 2

Related Questions