Nick Winner Nick Winner - 1 year ago 66
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.value, '' SEPARATOR '---') as notes,
GROUP_CONCAT(DISTINCT, '[-]', kk.value) as keywords
FROM contacts c
LEFT JOIN notes n ON LIKE n._contactID
( SELECT k.*, kc._contactID as contactID
FROM keywords k
INNER JOIN keywords_contacts kc ON LIKE kc._keywordID
) kk ON kk.contactID LIKE
ORDER BY c.`Last Name`, c.`First Name`

Answer Source

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

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download