Nick Winner Nick Winner - 3 months ago 13
MySQL Question

SQL query with multiple joins impacting performance

I have a relational MySQL database with almost 4000 records. The contacts table is related to both the keywords and notes tables with manny-to-many relationships. I wrote a query (with PHP) that would retrieve each contact record and, for each contact, all related notes and keywords in a group concat function. If I only grab the contacts, the query performs relatively quickly but with the two left joins and group concat, it takes almost 30 seconds. Is there a way to speed this up?

Here is my query:

SELECT c.*, GROUP_CONCAT(DISTINCT n.id, '[-]', n.value, '' SEPARATOR '---') as notes,
GROUP_CONCAT(DISTINCT kk.id, '[-]', kk.value) as keywords
FROM contacts c
LEFT JOIN notes n ON c.id LIKE n._contactID
LEFT JOIN
( SELECT k.*, kc._contactID as contactID
FROM keywords k
INNER JOIN keywords_contacts kc ON k.id LIKE kc._keywordID
) kk ON kk.contactID LIKE c.id
GROUP BY c.id
ORDER BY c.`Last Name`, c.`First Name`

Answer

I your query I can see c.id LIKE n._contactID, here LIKE is making performace low so use = operator and give index to foreign key field _contactID.

Same as for k.id LIKE kc._keywordID use = operator instead of LIKE and apply index on field _keywordID.