Did you ever find a solution to this?
Sep 16, 2014
2
Level 9
Adding indexes for a polymorphic relationship table in MySQL
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 or to participate in this conversation.