I’ve been fooling around with Rails the past week or two. I’m using postgresql for the development environment instead of sqlite, so I don’t end up in a situation where I regret using sqlite and have to migrate later.
Following allong the Getting Started guide, I created the second model, a Comment item on a blog post. This sets an article as a foreign key.
I ran the migration, created the table, and what do I see here? The migration both put the correct foreign key constraint on the article_id field, but then also indexed it. That’s smart if you want to load the “comments for this article” on the article page (you will).
So what this tells me is that when you are going to add a foreign key reference,
it probably makes sense to create an index on the referred item, as rails did here. This will greatly speed up the types of
queries I see all the time from wordpress, for example (made up example)
for some post number 100, loading the comments and details from the comments table.
Indexing commentmeta on comments, comments on posts speeds this up greatly.
Checking a database, I see this is actually done:
Wordpress 4.3 at least does do just this, adding the key (with a corresponding index) for the post_id and parent, since you will make queries using these values all the time. The Foreign key constraint is something you need to enforce via the application, but that’s because Mysql Implements Foreign Keys Differently, and it appears it ignores inline references.
I tested this, then had to look up why it didn’t give any warning about a specification it was ignoring: