Nothing is worse than scouring a table full of foreign keys, only to find out they aren’t indexed. I don’t care if you’re Jet Li hopped up on rails of coke and old episodes of Dragon Ball Z, you’re finds are still going to be slow and taxing. “But how do I look for missing indexes?” Please don’t ask me that, because every developer should have at least basic knowledge of SQL to even be considered useful in life. For those who still don’t know:
1 | SHOW INDEX FROM tablename; |
This can be quite tiresome if you want a quick overview, especially if you are working on a large project with many tables, so here is a snippet. It basically grabs all tables from the database (whether they are used in your project or not), then checks for all columns that look like foreign keys, and checks to see if they exist in any of the sites indexes, composite or not. Just paste this into your script/console:
1 2 3 4 5 | ActiveRecord::Base.connection.tables.each do |t| diff = ActiveRecord::Base.connection.columns(t).collect(&:name).grep(/_id/) - \ ActiveRecord::Base.connection.indexes(t).collect(&:columns).flatten puts "#{t}: #{diff.to_sentence}" if diff.any? end |
It’s not complex at all, and there might even be an easier way to do it, but to be honest, I couldn’t care less, it works for me. Here is a sample run on a current project after removing some of the indexes.
1 2 3 4 5 6 7 8 9 | >> ActiveRecord::Base.connection.tables.each do |t| ?> diff = ActiveRecord::Base.connection.columns(t).collect(&:name).grep(/_id/) - \ ?> ActiveRecord::Base.connection.indexes(t).collect(&:columns).flatten >> puts "#{t}: #{diff.to_sentence}" if diff.any? >> end albums: albumable_id ratings: user_id taggings: tagger_id titles: studio_id |
Like I said, this is simple and works, but could be better. Regardless, you should be doing an in depth index analysis on all your queries anyway, so really, you shouldn’t even need this, unless you’re taking over a project from a complete failure. Good day.
updated
My buddy @eladmeidar posted a thorough example using reflections to get an accurate list of indexes and what not. I don’t care to encourage your laziness, but he does. Thanks douche bag 
Related posts: