Rails - index for a query over three table columns

Question

I've got a query that I use often:

Site.where("mobile_visible = true AND (created_at > :date OR updated_at > :date)", :date => "12-04-30")

It produces this sql

SELECT `sites`.* FROM `sites` WHERE (mobile_visible = true AND (created_at > '12-04-30' OR updated_at > '12-04-30'))

I want to add an index or indexes to make this query more efficient. Should I add 3 indexes for the 3 columns separately or 1 index indexing all three columns separately?

Answer 1

The best approach is to construct an index that hits all elements of you're where clause -- not just one.

Databases generally can't use more than one index at a time for a particular portion of a query. If you add three indexes, the database will try to determine which one gives the greatest benefit and it will pick that one. It may or may not choose the best one depending on how the query execution plan is determined.

For this situation, I'd recommend adding the index:

add_index :sites, [:mobile_visible, :created_at, :updated_at]