Ambitious Query Indexer

Ambitious Query Indexer is a Rails plugin to identify database indexes that are missing. Rather than looking at tables and guessing what needs indexing, it will locate and run as many queries as it can find and suggest indexes that could be added based upon its findings.

This plugin is still in beta; at the moment there are various queries that it doesn’t understand and it could be better at finding methods in your application that run queries. That said, even now its recommendations should help your database load considerably.

I’m really eager for feedback on this plugin. If you have any thoughts or ideas, please let me know.

Installation

script/plugin install git://github.com/samdanavia/ambitious_query_indexer.git

Dependencies

This plugin uses the ‘multi’ gem. Install with:

sudo gem install multi

Usage

In the root of your Rails app, run the following:

rake ambitious_query_indexer:analyze

This plugin runs queries on your test database. Ensure that it is up-to-date (‘rake db:migrate RAILS_ENV=test’) and representative of your live schema for best results.

Testing

Run the following from the root of your Rails app:

rake test:plugins PLUGIN=ambitious_query_indexer

NB: This plugin’s tests create tables in your test database. Any tables created will be prefixed with ‘aqi_test’. In the unlikely event that you have a table that follows this same convention, you may see it trashed in the test environment.

Upcoming features/enhancements

General

• Auto-generation of migrations
• Highlighting redundant indexes
• Ability to pass individual finders or sql queries for index recommendations
• Support for common conventions in specific plugins – eg ‘current_user’ for RestfulAuthentication
• Support for ambitious_model_loader and similar plugins
• Statistics output – similar to rake stats
• Support for databases other than MySQL
• Other optimisation advice – common query smells etc

SQL Optimisation

Enhancements to the SQL optimisation engine:

• Support for lazier queries – implicit joins and implicit table references (‘field’ rather than ‘table.field’)
• Stronger support for conditionals – including FUNCTION() calls and references to other tables
• Support for difficult-to-index operators such as LIKE and RLIKE
• Support for HAVING
• Support for JOIN ON
• Support for aliased tables (AS)

Object Call location

Enhancements to the code that deals with finding the methods that run queries:

• Better support for associations and named_scopes and other methods which run queries
• Support for commented-out lines

Feedback

All feedback, bug reports and thoughts on this gratefully received.

Copyright © 2009 Sam Phillips, released under the MIT license