I'm creating indexes for my models right now and I want to know how MySQL and PostgreSQL deal with an index with more than 1 column like:

add_index :users, [:username, :created_at]

That should utilize the index when I do a query like (I think):

User.where("username = ? and created_at > ?", 'jim', DateTime.now.yesterday)

But will it also utilize the index if I only use the username in a query?

User.where("username = ?", 'jim')

In PostgreSQL at least, multi-column indexes are usable for single-column searches on the left column, but not for single-column searches on the right column.

So: an index on (a,b,c) can be used for searches on a, a,b and a,b,c but not for searches on b,c or c.

If search patterns make individually usable indexes on all columns necessary and if the column values are small, people often create indexes a,b,c, b,c and c.

An alternative can be individual indexes on a, b, and c. PostgreSQL can use a bitmap index scan in many circumstances to combine the indexes, so a search with conditions on b and c will use both the indexes on b and c.

Rather than guess, the best thing to do is use EXPLAIN ANALYZE or the auto_explain module to examine the query plans and run-time stats from your queries, so you can see what's actually happening.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.