I have a query which combines a LEFT JOIN and subquery. The dataset is quite big and the time to execute the statement is over 70 seconds.
SELECT s.siblings, l.id FROM `list` l INNER JOIN child c ON c.id = l.child_id INNER JOIN parent p ON p.id = c.parent_id LEFT JOIN ( SELECT COUNT(c.id) AS siblings, c.id, c.parent_id FROM child c GROUP BY c.id ) AS s ON s.parent_id = c.parent_id AND s.id != c.id WHERE l.country = 1 GROUP BY l.id, s.siblings ORDER BY l.dateadded
This query should return all lists for a country. Each list is specific to a unique baby. For each list I would like to return a count of the number of children that have the same parent.
If I remove the LEFT JOIN subquery the fetch time is 0.1 seconds. Is there a way to make the query more efficient?