Introducing Ambitious Query Indexer – A new way to index your Rails app’s database
Sam Phillips, October 5th, 2009 9:13 pm
Relational databases like MySQL rely on correct indexing of tables in order to function quickly. In fact, adding proper database indexes is often one of the biggest improvements you can make to any application, including Rails applications.
A few well-known blog posts have dealt with this subject, giving excellent advice on how to index your tables for best performance. Jamis dealt with the subject a while ago, and recently Matt Jankowski has posted a fairly exhaustive guide. I felt that this excellent guides could be built upon, and the analysis that I would do, given a query to index, could be automated.
Enter Ambitious Query Indexer. Unlike other plugins in this space, AQI (I’m starting to shorten it to ‘aqute’; not sure if this will stick) doesn’t optimise queries on the fly by reducing eager loading, and it doesn’t look at tables in an attempt to guess what indexes might be needed to query it.
AQI’s approach is to analyse your Rails app, work out what queries it will run during its operation and spot any indexes that could be added for the MySQL engine to use. Why is this a good approach? Simply, because it focuses on the queries you run, not the tables they run upon. Imagine the following story:
- You have an Article model containing a user_id field in an articles table
- You have a User model that has_many articles
- Calling User.first.articles will run a query like “SELECT * FROM articles WHERE user_id = ‘1′”.
How do you speed this query up? You would add an index to the articles table on the user_id field. Happy days.
Now imagine you install acts_as_paranoid on the Article model or something similar – a plugin that overrides the default finders – suddenly, your query will be something like:
SELECT * FROM articles WHERE user_id = '1' AND (deleted_at IS NULL or deleted_at < '2009-10-05 22:07:00')
Whoops - your index doesn't work any more because you've added a field to the query. Your database is about to go to the dogs, and only a cursory look at the table wouldn't reveal why because it's not immediately obvious that you now need a compound index on user_id and deleted_at.
Unfortunately, when it comes to indexing, looking at a table to work out what indexes you need is not enough. Adding indexes during your migrations won't always cover you, and best practice indexing won't fly on most real-world applications. You have to look at the queries that interrogate that table. AQI will do this; it will find, run and analyse your queries and give you a list of indexes that will improve performance. Yay!
To install, follow the easy instructions on GitHub - Ambitious Query Indexer. This plugin is still under active development and I'm adding new features all the time. Please let me know any feedback you have, or any issues you encounter.
