CODEWITHSUNDEEP
mysqlsqljoingroup-bycount
Ganesh Aher
Ganesh Aher

Reputation: 1128

How to get result for count from two tables

I have two tables, Cars and Defect. I'm maintaining relation by passing car_id to defect table as foreign key.

I've 3000 records in cars table and 16K records in defect tables (having open_defect and close_defect). I'm trying to find out all Cars(3000) with count of open defects (if there is no any open defect against car should return 0).

I'm trying some queries:

SELECT cars.cars_id_primary ,IF(COUNT(defect.defect_id_primary)>0,1,0) AS `def_count`
FROM cars
LEFT JOIN defect ON cars.cars_id_primary = defect.cars_id AND defect.defect_status_id =1
WHERE cars.stage_id !=5 
GROUP BY cars.cars_id_primary
ORDER BY cars.updated_on

This query gives me result but takes too much time to execute. Need to optimize this query. I'm stuck to optimize.

Any help welcomes, Thanks in advance.

Upvotes: 0

Views: 41

Answers (3)

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

You can increase the performance by using indexes. Can you create an index on cars_id_primary of cars table and index on cars_id of defect like below sql. Then you can try your query.

CREATE INDEX idx1 ON cars (cars_id_primary);
CREATE INDEX idx2 ON defect (cars_id);

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272106

No need to involve cars table inside GROUP BY. Rewrite your query like so:

SELECT cars.cars_id_primary, COALESCE(agg.open_defect_count, 0) AS open_defect_count
FROM cars
LEFT JOIN (
    SELECT cars_id, COUNT(*) AS open_defect_count
    FROM defect
    WHERE defect_status_id = 1
    GROUP BY cars_id
) AS agg ON cars.cars_id_primary = agg.cars_id
WHERE cars.stage_id != 5 
ORDER BY cars.updated_on

You will need to create indexes as well. I suggest starting with ix_defect(defect_status_id, cars_id).

Upvotes: 2

Ram
Ram

Reputation: 84

I can't understand your question, I made a little bit changes in your query,

SELECT cars.cars_id_primary ,COUNT(CASE WHEN (defect.defect_id_primary)>0 THEN 1 ELSE 0 
   END) AS `def_count`
FROM cars
LEFT JOIN defect ON cars.cars_id_primary = defect.cars_id 
WHERE cars.stage_id !=5 AND defect.defect_status_id =1 
GROUP BY cars.cars_id_primary
ORDER BY cars.updated_on

try this

Upvotes: 0

Related Questions