Take N latest records without using orderBy

Question

I'm having an optimization problem, where in need to take n latest results from database that has a lot of records. I know I can do it by using orderBy, but I want to avoid it, because orderBy loops through every record, which increases my load & query times. I found one answer that uses negative number in take() which basically takes latest records, but it's not working for me, it takes all the records regardless:

Record::all()->take(-5);

This is the output of previous code, which only returns empty Query Builder instance:

  "supplementRecommendationsHistory" => Illuminate\Database\Eloquent\Builder {#2485 ▼
    #query: Illuminate\Database\Query\Builder {#2484 ▶}
    #model: App\RecommendedSupplement {#2483 ▶}
    #eagerLoad: array:2 [▶]
    #localMacros: []
    #onDelete: null
    #passthru: array:17 [▶]
    #scopes: []
    #removedScopes: []
  }

Is there a different aproach to this? Appreciate any help.

Simply `latest` indicates that you want ordering to be made. If you have auto-increment column, then find next ID from table schema and do `WHERE id > :{nextIndex - N}` — Justinas, Mar 12 '21 at 11:29
@Justinas is exactly right. Think of your table as an unordered set of rows. You necessarily must use `orderBy` unless you want your results in an unpredictable order. The good news? when you `orderBy` an indexed column in your table it's not nearly as expensive as you assume. — O. Jones, Mar 12 '21 at 11:34
See this article about group by / limit optimization in mysql https://www.percona.com/blog/2006/09/01/mysql-order-by-limit-performance-optimization/ — Martin Osusky, Mar 12 '21 at 12:26
Thanks for your suggestions guys. I went with @Justinas suggestion ```WHERE id > :{nextIndex - N}``` and it seems to work for now. — zlatan, Mar 12 '21 at 12:33
@zlatan Can you post implementation as answer? Because obviously `WHERE id > :{nextIndex - N}` as plain query will never work — Justinas, Mar 12 '21 at 12:39
What kind of database are you using? MySQL? PostgreSQL? SQLServer? — IGP, Mar 12 '21 at 16:56
I added my solution down below, sorry for the late response. — zlatan, Mar 13 '21 at 16:16

score 0 · Accepted Answer · answered Mar 13 '21 at 16:16

Following suggestion from the comments (@Justinas), this is how I implemented WHERE id > :{nextIndex - N} solution:

RecommendedTest::where('id', '>', RecommendedTest::max('id') - 20)->get()->reverse()->values();

This way I could get latest 20 records from a huge database. I also used reverse()->values() to revert ordering of the results, which basically gave me results sorted by created_at column, which was my initial goal. The query didn't take long, around ~1000ms, which was okay for me, as this was temporary solution.

Documentation for the reverse and values methods

Take N latest records without using orderBy

1 Answers1