Between the time your migration files get generated and deploying to production with your new application you will likely need to add indexes to one or more of your tables. How do you know which tables need indexes and which fields in those tables to index?
Here are some tips to help figure out where the likely bottlenecks will occur with regard to your database tables:
- Always index @*_id@ foreign_key fields. Let’s say you have 2 tables, @users@ and @addresses@. You may have migration file code in @./db/migrate@ that looks like this:
...
def self.up
create_table "users", :force => true do |t|
t.column :login, :email :string
end
end
... ...
def self.up
create_table "addresses", :force => true do |t|
t.column :user_id :integer
t.column :address :string
end
end
...You will always want to index the foreign_key @user_id@, with few exceptions. So simply add the @add_index@ line like this:
...
def self.up
create_table "addresses", :force => true do |t|
t.column :user_id :integer
t.column :address :string
end
add_index "addresses", :user_id
end
...This will facilitate much faster associations when you lookup related models like this view code:
<%=h user.address.address %>
# OR as the case may be...
<%=h address.user.email %>- As a rule of thumb index any column for which you will be using to lookup a row or set of rows. For example, if you will be doing something like this:
u = User.find_by_email('test@example.com')… then you will want to index the email field.
- Run explain plans on your associations to determine where indexes would benefit the queries. Now this one is more tricky unless you know about the query_analyzer plugin. This nifty little tool will automatically dump out explain plans into your logfiles in development mode that look like this example from the README file:
# development.log P Load (0.008669) => SELECT p.* FROM p INNER JOIN d ON p.id = d.p_id WHERE (d.p_id = 2 AND ((d.type = 'P'))) Analyzing P Load select_type | key_len | type | Extra | id | possible_keys | rows | table | ref | key ---------------------------------------------------------------------------------------------------- SIMPLE | | ALL | Using where | 1 | | 74 | d | | SIMPLE | 4 | eq_ref | Using where | 1 | PRIMARY | 1 | p | d.p_id | PRIMARY
Now what this tells you is that the table @d@ is missing an index because MySQL is having to look at “ALL” the records. Whenever you see a @type@ of @ALL@ and/or @possible_keys@ is empty it should be a red flag telling you to index something.
If we had followed tips 1 and 2 this explain plan would be much different, showing reference types of @ref@ and @eq_ref@ which is typically a good thing.
For more information see the README file here: http://agilewebdevelopment.com/plugins/query_analyzer.
If you are getting an error message like this:
ActiveRecord::StatementInvalid: Mysql::Error: Table configurable_settings doesn't exist
… when trying to use Jacob Radford’s http://agilewebdevelopment.com/plugins/acts_as_configurable
Just do this:
./script/console development
ConfigurableSetting.create_table
Of course replace ‘development’ above with whatever environment you need.
If you are getting this error message when trying to use RoR ActiveRecord, there is a simple workaround… read on.
MySQL has a problem with how Rails sends the username and password when initiating the connection through the socket.
A quick fix/workaround is to create a MySQL user without a password. Add it to your database.yml file and restart your Mongrel/Webrick server.