Database: Scopes, migrations, and indices

Updated . Posted . Visible to the public. Repeats.

Wether you modify an existing named scope or add a new one, or when you write a new query, make sure you have the proper indices.

This particularly applies if you're going to run non-trivial queries of course (admin backends, analytics, etc).

Compound indices

A chain of scopes results in (usually) one query. You should take into account all attributes (columns) that are used in :conditions, :join, :group, :having, and :order, as all those result in filtering and sorting–slow operations without indices.

Take the list of all those attributes: the table should have at least one compound index that includes all those attributes.
It can sometimes be enough to already have an index on a subset of your query's columns, but it is not enough to have 2 indices covering all your columns

If not, add a new index.

Index order

Ordering keys in indices is important: in general, order columns in your index from lowest to highest cardinality, typically flags and enumerations first, then foreign keys, timestamps, and finally your table's primary key (:id).

Using parts of indices

When looking for an index to use, MySQL only use leftmost parts of compound indices. For instance, if querying on columns a, b, and c, an index on (a,b) will be used, but not an index on (b,c).

Issues with sorting

When sorting, only leftmost part of indices are used. To optimize sorting you'll need an index that starts with your sorting criteria in the same order.

For instance, if sorting with :order => "created_at DESC, id DESC you need a compound index that looks like:

 add_index :bookings, [:created_at, :id]

Sort direction is important too. Don't mix ASC and DESC or no indices will be used.

Using EXPLAIN

All the above is more guidelines than general rules.

To figure out whether your query's efficient or not, just run your query prefixed with EXPLAIN.
If it tells you it's using temporary tables or filesort... your query's probably not going to be very fast.

Example query

SELECT `bookings`.*
     FROM `bookings`
INNER JOIN `payments`
    ON payments.booking_id = bookings.id
WHERE (bookings.is_settlement_booking OR bookings.status IN (?,?,?,?,?,?,?,?,?) OR payments.used_for_authorisation = ?)
GROUP BY bookings.id
ORDER BY bookings.created_at DESC, bookings.id DESC
LIMIT ?, ?

Efficient index on bookings:

add_index :bookings, [:is_settlement_booking, :status, :created_at, :id]
Julien Letessier
Last edit
Posted by Julien Letessier to HouseTrip Deck (2012-09-26 10:34)