Useful things you should know about Mysql

I discovered a useful article by Ian Gilfillan on my disk earlier today. After scanning it I realized that pretty much everything he said was important if you’re doing anything with Mysql.

Here’s a tip:

Index your tables

Appropriate indices drastically speed up your Mysql select queries. Adding an index to an existing table is made easy with Ruby on Rails migrations. First we generate the migration:

$ ./script/generate migration add_indices_to_the_posts_table
      exists  db/migrate
      create  db/migrate/20090717134840_add_indices_table_posts.rb

Next we add our code:

class AddIndicesTablePosts < ActiveRecord::Migration
  def self.up
    add_index :posts, :member_id
    add_index :posts, :permalink
  end
  def self.down
    remove_index :posts, :member_id
    remove_index :posts, :permalink
  end
end

Indices are useful whenever SELECT queries contain an ORDER BY or WHERE clause. This means that if nothing else, indexing your foreign keys is very important (eg. posts.member_id above.)

Everyone loves a human-readable URL so direct links to our posts are of the form http://mydomain.com/posts/hello-world instead of the Rails-default http://mydomain.com/posts/1. As such we’ll be using Post.find_by_permalink(params[:permalink]) quite a lot so we added another index to the permalink field.

Mysql EXPLAIN

Sure this makes sense but this kind of reasoning will only take you so far. If you suspect a slow query is the reason for a bottleneck in your application you should use the Mysql EXPLAIN keyword to find out exactly how Mysql is going about finding your records.

I’ve adapted the ‘query-analyzer rails plugin’ by Bob Silva to make this really easy. First install the explain-query gem by issuing the following commands:

$ gem sources -a http://gems.github.com/
$ gem install g00k-explain-query

Then start up your Rails console session and start EXPLAINing your queries:

$ ./script/console
  Loading development environment (Rails 2.3.2)
  >> require 'explain-query'
  >> Post.explain do
  >> Post.find_by_permalink("first-post")
  >> end
SELECT * FROM `posts` WHERE (`posts`.`permalink` = 'first-post')  LIMIT 1
 select_type | key_len | table | id | possible_keys | type | Extra       | rows | ref | key
 ------------------------------------------------------------------------------------------
 SIMPLE      |         | posts | 1  |               | ALL  | Using where | 6    |     |

As you can see my posts table isn’t indexed yet so Mysql checks ‘ALL’ reocrds in the posts table. After running the migration posted above, this is how the same query looks:

>> Post.explain do
?> Post.find_by_permalink("first-post")
>> end
SELECT * FROM `posts` WHERE (`posts`.`permalink` = 'first-post')  LIMIT 1
select_type | key_len | table | id | possible_keys            | type | Extra       | rows | ref   | key
----------------------------------------------------------------------------------------------------------------------------
SIMPLE      | 768     | posts | 1  | index_posts_on_permalink | ref  | Using where | 1    | const | index_posts_on_permalink

That’s much better! The index on our posts table’s permalink field allows Mysql to jump straight to the row we’re curious about.

There’s a lot to know about indexing your tables and using those indices effectively. You should definitely check out the following article by Ian Gilfillan as well as the 2 follow-up articles for more on optimizing Mysql: Optimizing Mysql: Queries and Indexes.