LStarky LStarky - 3 years ago 222
MySQL Question

MySQL SELECT query that counts left joined rows takes too long

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

Explain results

Answer Source
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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download