Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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')
share|improve this question
add comment

migrated from stackoverflow.com Aug 19 '13 at 16:31

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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