Rails/Indexes performance Q - which is faster?

Question

Table has columns: user_id and season_id
Which is faster:

#If I have a database index on user_id
Table.find_all_by_user_id(some_id)

or

#If I have a database index on user_id and season_id
Table.find_all_by_user_id_and_season_id(some_id, another_id)

Is a multi-column index always faster?
For example, is writing a multi-column index slower? If so, I could use the single-column query.

PS. The table has about 1.000.000 records and grows steadily!

Answer 1

The index depends on the query you need to perform. The following code

Table.find_all_by_user_id(some_id)

doesn't add any index. The following does

add_index :table, :column

So, if you need to perform queries using both user_id and season_id, then add a composite index. Otherwise add a simple index.

Answer 2

Table.find_all_by... has nothing to do with indexes. These are added by rails regardless of whether a column is a foreign key or indexed. If you want these columns to be indexed by the database, you will need to add them manually with add_index in a migration.

Your examples are doing two completely different things. Once is finding by one column, the other is finding by two columns, and they will return different results.

If you want to know which is faster, you will have to do some benchmarking. I recommend reading the rails guide for this. http://guides.rubyonrails.org/performance_testing.html