Jim Neath

Manchester based Ruby on Rails & Facebook App Developer

Fixing Long Key Lengths in MySQL

While using Query Reviewer, I occasionally come across a warning along the lines of the following:

2 identical queries Table videos: Long key length (768)

With the following explanation:

The key used for the index was rather long, potentially affecting indices in memory

To fix this we need to limit the length of our index. We can do this by creating a migration with :length added to the add_index method:

add_index :videos, :cached_slug, :length => 20

If you’re indexing more than one column, you can use the following:

add_index :stories, [:title, :author_name], :length => { :title => 10, :author_name => 5 }

Hopefully this post will save someone a few minutes.

blog comments powered by Disqus

I am available for freelance work! Click here to email me.

Jim Neath is a Freelance Ruby on Rails & Facebook app developer from Manchester, UK, currently working for Engine Yard.