Reputation: 377
The following query is running very slow...
SELECT
CONCAT(users.first_name, ' ', users.last_name) AS user_name,
leads.first_name AS first_name,
comments.*,
FROM comments
INNER JOIN users ON
users.id = comments.user_id
INNER JOIN leads ON
leads.id = comments.lead_id AND
leads.company_id = 1
ORDER BY
`sort` DESC,
reply ASC,
id ASC
LIMIT 80,20
leads table has ~ 8000 records users table has ~ 300 records comments table has ~ 500,000 records
I have an index on leads.id, comments.lead_id, leads.company_id, users.id, and comments.user_id, comments.sort, comments.reply, comments.id
Can someone explain how to optimize this query?
Upvotes: 0
Views: 56
Reputation: 425013
Move leads
to first in the from
list:
SELECT
CONCAT(users.first_name, ' ', users.last_name) AS user_name,
leads.first_name AS first_name,
comments.*
FROM leads
INNER JOIN comments ON
comments.lead_id = leads.id
INNER JOIN users ON
users.id = comments.user_id
WHERE leads.company_id = 1
ORDER BY
`sort` DESC,
reply ASC,
id ASC
LIMIT 80,20
This allows the index on leads(company)
to immediately filter out as many rows as possible before joining to the other tables.
The performance increase should be about 1/(fraction of leads with company_id = 1)
Upvotes: 1