rails - created_at when user for ordering, Should you add an Index to the table?

The following is based on my PostgreSQL experience but would probably also apply for MySQL and others.

If you plan on retrieving a large number of records from this table or making use of pagination, an index on the fields used in the ORDER BY would be useful.

You should create an index on all order fields in the same order. If you are mixing ASC and DESC in your ORDER BY you would need to create an index with these specific orderings to take full advantage of the index.

A suitable ActiveRecord migration for your photos table would be:

add_index :photos, [:created_at, :version]

I would recommend looking at the EXPLAIN ANALYZE output with production-like data before and after adding the index to confirm it is having the effect you are after.

One trick that may be helpful: in typical MySql/ActiveRecord models, you will have id as a numeric primary key (hence implicitly indexed), which should be in the same order of created_at - which means you could order by id instead and get the fast performance, without the write cost for an extra index.

Was just reading up on this last night as I was adding indices to a number of tables. The short answer is 'yes'. The case is quite eloquently made here:

https://web.archive.org/web/20140426154227/https://tomafro.net/2009/08/using-indexes-in-rails-choosing-additional-indexes

The longer answer is also 'yes' but why don't you test it out a bit and see what sort of mileage you get out of it based on how your app interacts with the data.