Ben
Ben

Reputation: 377

How to best join table using indexes

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?

Screenshot of query EXPLAIN

Upvotes: 0

Views: 56

Answers (1)

Bohemian
Bohemian

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

Related Questions