MySQL SELECT query that counts left joined rows takes too long

Question

Does anyone know how to optimize this query?

SELECT planbook.*,

  COUNT(pb_unit_id) AS total_units,

  COUNT(pb_lsn_id) AS total_lessons

FROM planbook

  LEFT JOIN planbook_unit ON pb_unit_pb_id = pb_id

  LEFT JOIN planbook_lesson ON pb_lsn_pb_id = pb_id

WHERE pb_site_id = 1

GROUP BY pb_id

The slow part is getting the total number of matching units and lessons. I have indexes on the following fields (and others):

planbook.pb_id

planbook_unit.pb_unit_pb_id

planbook_lesson.pb_lsn_pb_id

My only objective is to get the total number of matching units and lessons along with the details of each planbook row.

However, this query is taking around 35 seconds. I have 1625 records in planbook, 13,693 records in planbook_unit, and 122,950 records in planbook_lesson.

Any suggestions?

Edit: Explain Results

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

SELECT  planbook.*,
        ( SELECT COUNT(*) FROM planbook_unit
               WHERE pb_unit_pb_id = planbook.pb_id ) AS total_units,
        ( SELECT COUNT(*) FROM planbook_lesson
               WHERE pb_lsn_pb_id  = planbook.pb_id ) AS total_lessons
    FROM  planbook
    WHERE  pb_site_id = 1

planbook:        INDEX(pb_site_id)
planbook_unit:   INDEX(pb_unit_pb_id)
planbook_lesson: INDEX(pb_lsn_pb_id)

MySQL SELECT query that counts left joined rows takes too long

Sign up