marlek

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.

Laracasts Mascot

Hi, Have We Met Yet?

Did you know that, in addition to the forum, Laracasts includes well over 1000 lessons on modern web development? All for the price of one lunch out per month.

Sign Me Up

Channels

Reply to

Use Markdown with GitHub-flavored code blocks.