sql - take vs first performance in Ruby on Rails

Question

This is a question regarding ActiveRecord query methods:

• first Find the first record (or first N records if a parameter is supplied). If no order is defined it will order by primary key.
• take Gives a record (or N records if a parameter is supplied) without any implied order. The order will depend on the database implementation. If an order is supplied it will be respected.

usecase: retrieve record from database based on unique attribute, example.

User.where(email: '[email protected]')

here, first generates

SELECT "users".* FROM "users" WHERE "users"."email" = '[email protected]' ORDER BY "users"."id"` ASC LIMIT 1

take generates

SELECT "users".* FROM "users" WHERE "users"."email" = '[email protected]' LIMIT 1

so as seen above first adds additional ordering clause. I am wondering if there a performance difference between take vs first.

Is take faster than first or vice-versa?

Answer 1

In general "take" will be faster, because the database does not have to identify all of the rows that meet the criteria and then sort them and find the lowest-sorting row. "take" allows the database to stop as soon as it has found a single row.

The degree to which it is faster is going to vary according to:

1. How much time is saved in not having to look for more than one row. The worst case here is where a full scan of a large table is required, but one matching row is found very early in the scan. "take" would allow the scan to be stopped.

2. How many rows would need to be sorted to find the one with the lowest id. The worst case here is where every row in the table matches the criteria and needs to be included in the sort.

There are some other factors to consider -- for example for a "first" query the optimiser might be able to access the table via a scan of the primary key index and check each row to see if it matches the condition. If there is a very high likelihood of that then both a complete scan of the data and a sort can be avoided if the query optimiser is sophisticated enough.

In many cases, where there are very few matching records and index-based access to find them, you'll find that the difference is trivial (where there is a unique index on "email" in your example). However, I would still use "take" in preference to first even then.

Edit: I'll just add, though it's a little off-topic, that in your example you might as well use:

User.find_by(email: '[email protected]')

The generated query should be exactly the same as for take, but the semantics are a bit more clear I think.