Database: Scopes, migrations, and indices
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).
A chain of scopes results in (usually) one query. You should take into account all attributes (columns) that are used in
: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.
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 (
When looking for an index to use, MySQL only use leftmost parts of compound indices. For instance, if querying on columns
c, an index on
(a,b) will be used, but not an index on
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
DESC or no indices will be used.
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.
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
add_index :bookings, [:is_settlement_booking, :status, :created_at, :id]