Optimise MySQL SELECT with LEFT JOIN subquery

Question

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?

Answer 1

The main reason for the slow query is the join on a subquery. This will not use indexes. Then, you not only join with a derived table (subquery), but as well group total result based on subquery column - GROUP BY l.id, s.Siblings

In this case it could help to:

• create temporary table from subquery, it also could include subquery for return correct parent_id
• create index on this table
• use temporary table in join
• drop temporary table

This could have variants, but it is often faster and less server-loading than a complicated set of subqueries with joins.

Answer 2

The query has lot of unnecessary complications. Plus the GROUP BY c.id in the derived table (assuming that child (id) is the primary key of that table) seems completely redundant. The result of the query should be always 1 for the siblings column, which is probably not the wanted result.

This would do what you want (find the number of siblings) in a much simpler way:

SELECT
    s.cnt - 1 AS siblings,
    l.id
FROM
    `list` AS l
        INNER JOIN
    child AS c ON c.id = l.child_id
        INNER JOIN 
    ( SELECT c.parent_id, COUNT(*) AS cnt
      FROM child AS c
      GROUP BY c.parent_id
    ) AS s ON s.parent_id = c.parent_id
WHERE
    l.country = 1
ORDER BY l.dateadded ;

Answer 3

s.siblings,
...
JOIN ( SELECT ... )
  -->
( SELECT COUNT(*) FROM ... ) AS siblings

That is, get rid of the LEFT JOIN and replace it with a subquery in the SELECT.