Rails developers aren't exactly known for getting their indexes right (or even at all) on their databases. Granted, databases are a tough subject, and some people and companies make their living dealing with only databases, and some only with one database (like MySQL or Oracle).

If you're coming to web development with no formal background in databases, and it's all new to you, then it's totally understandable to maybe forget about indexes initially, but luckily they can be added later. Not adding indexes right away can be a benefit as well, since you can see what your application is doing, and index only what you need.

Luckily pretty much all of the databases are smart and can tell you if queries are using indexes or not, but you still have to ask. In MySQL land, this is done with the EXPLAIN syntax. The docs for it are short and sweet, but basically you just feed it a SELECT query and it gives you some nice output. As an example, here's the result of EXPLAIN SELECT * FROM tf_users WHERE user_id = 'darkhelmet'; on the users table from TorrentFlux. ¹

As you can see, it has the column named possible_keys which is NULL, as well as the key column, also showing NULL.²

Bad DBA

SHOW INDEX

If you run SHOW INDEX on the database, it will tell you what indexes you already have:

This one just has the primary key on the uid column. If your app is actually finding people by user_id (and TorrentFlux does when you login), this is a big slowdown when you have lots of users. Any query not using an index will take longer than a query using proper indexes. As a downside, indexes result in slightly slower inserts (more so if the index is unique) and require more disk space to store. More RAM is also needed, but frankly the pros outweigh the cons.

Anyway.

CREATE INDEX

If we add an index to this table, we can see the differences:

Now possible_keys and keys are populated with the key name we set up.

Now what?

Okay so the title of this post is Find queries missing indexes in your Rails application, and so far I've just explained why you should use indexes and shown some MySQL syntax to make them. Here comes the Rails part.

Put that in your script directory and smoke it. Seriously. It's for Rails 3, but I'm sure you can change that top line to make it work in Rails 2.x.

This script goes through the log file for your current Rails environment, grabs all the SELECT queries and uses the Soundex algorithm explained here to weed out queries that are similar enough. In my test it took 2000+ queries down to about 300. There will still be some duplicates, but it's close enough. It then feeds them all to the database with EXPLAIN stuffed in front, and checks if the key column is nil, and prints out any queries that don't have indexes. You can then at your leisure feed them to your database, show the indexes and figure out what you need to add to make the query happy. Add a migration and you're set.

You could even modify this script to be able to run when you run unit tests and fail if a query doesn't have an index.

So get on it. You don't have an excuse anymore.