mysqlsqlindexingcompound-index

What is compound indexing and how do I use it properly?

I have a really slow query that repeats itself quite a bit. I've tried indexing the individual fields but it doesn't seem to help. The CPU usage is still very high and the queries still appear on the slow query log. It seems I need a compound index?

How would I index the following query properly? 

select *
from `to_attachments` left join
     `attachments`
     on `to_attachments`.`attachment_id` = `attachments`.`id`
where `to_attachments`.`object_type` = 'communicator' and `to_attachments`.`object_id` = '64328'
order by `attachments`.`created_at` desc;

EXPLAIN Result: 1 SIMPLE to_attachments index NULL PRIMARY 775 NULL 244384 Using where; Using index; Using temporary; Using filesort 1 SIMPLE attachments eq_ref PRIMARY PRIMARY 4 quote.to_attachments.attachment_id 1 NULL

Index For to_attachments enter image description here

Solution

  • You want indexes on to_attachments(object_type, object_id, attachment_id) and attachments(id).