Ruby's .where vs. detect

Question

I'm looking for a method that is faster and uses less server processing. In my application, I can use both .where and .detect:

Where:

User.where(id: 1)
# User Load (0.5ms)

Detect:

User.all.detect{ |u| u.id == 1 }
# User Load (0.7ms). Sometimes increases more than .where

I understand that .detect returns the first item in the list for which the block returns TRUE but how does it compares with .where if I have thousands of Users?

Edited for clarity.

.where is used in this example because I may not query for the id alone. What if I have a table column called "name"?

Answer 1

In this example

User.find(1)        # or
User.find_by(id: 1)

will be the fastest solutions. Because both queries tell the database to return exactly one record with a matching id. As soon as the database finds a matching record, it doesn't look further but returns that one record immediately.

Whereas

User.where(id: 1)

would return an array of objects matching the condition. That means: After a matching record was found the database would continue looking for other records to match the query and therefore always scan the whole database table. In this case – since id is very likely a column with unique values – it would return an array with only one instance.

In opposite to

User.all.detect { |u| u.id == 1 }

that would load all users from the database. This will result in loading thousands of users into memory, building ActiveRecord instances, iterating over that array and then throwing away all records that do not match the condition. This will be very slow compared to just loading matching records from the database.

Database management systems are optimized to run selection queries and you can improve their ability to do so by designing a useful schema and adding appropriate indexes. Every record loaded from the database will need to be translated into an instance of ActiveRecord and will consume memory - both operations are not for free. Therefore the rule of thumb should be: Whenever possible run queries directly in the database instead of in Ruby.