Adding indexes for a polymorphic relationship table in MySQL

Posted 4 years ago by marlek

Hi, Here's an example scenario that mocks my problem:

I need to index columns in the pivot table editors between User and Page, which have a polymorphic relationship. I'm saving a person who edited the Page (and will do the same for Post or some other Object.

Here's the table:

user_id (int),
editable_id (int),
editable_type (varchar),
edited_at (timestamp)

And here's the query produced by Laravel to fetch editors for a Page:

select `users`.*, `editors`.`editable_id` as `pivot_editable_id`, `editors`.`user_id` as `pivot_user_id`, `editors`.`edited_at` as `pivot_edited_at` 
from `users` 
inner join `editors` 
on `users`.`id` = `editors`.`user_id` 
where `editors`.`editable_id` = '1175' and `editors`.`editable_type` = 'Page'
order by `id` desc limit 25

I would like to know if there's (an there probably is) a better way of adding indexes for this table then just indexing all three columns (user_id, editable_id, editable_type)

Please sign in or create an account to participate in this conversation.