0

I need to find the most recently created record in a table/Rails model, and I'd like to do it in an efficient manner. My primary key is a UUID so using the Rails Samples.last method won't work. Googling for this, the top Rails answers use a sort ordered in descending order with a limit of 1. Of course this isn't the only way to do it, and when I took databases classes a few years ago we frequently did this sort of thing with a left outer join.

I tried both methods out in SQLServer but I'm not that familiar with explain and can't tell which is better. I tried:

SELECT TOP 1 * FROM samples ORDER BY created_at DESC;

This explain showed a sort using a clustered index scan.

SELECT * FROM samples s1 
LEFT OUTER JOIN samples s2 ON s1.created_at < s2.created_at 
WHERE s2.user_id IS NULL;

The explain for that one shows a filter with a nested loop using two clustered index scans.

Which of these is better, or are they comparable? Or is there a different method I should be using? The sorting version seems clearer to me, so my inclination is to go for that one (other developers on my team know even less about SQL and databases than I do), but this will be used in large tables so it needs to be efficient.

Also if it's important I used SQLServer for this but I also have MySQL databases so I'm hoping for a database-agnostic answer, if possible.

Community
  • 1
  • 1
Maltiriel
  • 793
  • 2
  • 11
  • 28

3 Answers3

1

Assuming that your id column has an index and being a primary key, you could safely (?) assume that the newest record would be the one with the highest id number?

The query would be:

SELECT * FROM samples ORDER BY id DESC LIMIT 1;

Or, you can use Sample.last, which would do it for you.

Edit:

Since your id is a uuid, I would recommend adding an index to the created_at column, to avoid full-table scans. Then, to make your life easier, you can add the following scopes to your model:

scope :first, -> { order("created_at").first }
scope :last, -> { order("created_at DESC").first }

Hope this helps!

Ilija Eftimov
  • 790
  • 8
  • 16
  • 1
    http://stackoverflow.com/questions/9780169/active-record-model-find-last That assumes an integer for a primary key. Mine is a UUID. I'll edit the question to reflect this fact. – Maltiriel Jul 11 '16 at 22:26
  • The question isn't "how can I do this in Rails". I know how to do it already. The question is "what is the most efficient way". The default Rails way to do things doesn't necessarily result in the most efficient database queries. – Maltiriel Jul 11 '16 at 22:33
  • As I said, I recommend adding an index to your `created_at` column. I see that you added the `left join` query but I don't see how that can be quicker than a plain index on the column. – Ilija Eftimov Jul 11 '16 at 22:35
  • 1
    I think this is the most efficient approach. The LEFT OUTER JOIN itself couldn't have been an efficient approach. I'm not totally sure of the full table scan point, though. Moreover, you mentioned the need for a `database-agnostic` approach, which definitely is this. – oreoluwa Jul 11 '16 at 22:39
0

Since you're using Ruby on Rails, have you tried using Sample.last ?

You can read more here: Finder Methods: Last

Okomikeruko
  • 1,123
  • 10
  • 22
  • http://stackoverflow.com/questions/9780169/active-record-model-find-last My primary key is a UUID, so this won't work. – Maltiriel Jul 11 '16 at 22:25
0

I think you can do:

Sample.order(created_at: :desc).first
oreoluwa
  • 5,553
  • 2
  • 20
  • 27