Laravel Query Builder orderBy id DESC is very slow on 100k rows

Question

I have a DB filled with 100k products, here is my query

 $products = DB::table('product')
    ->leftJoin('product_description', 'product_description.product_id', 'product.product_id')
    ->leftJoin('product_to_category','product_to_category.product_id','product.product_id')
    ->select(self::$select_fields)
    ->where('product_description.language', $language)
    ->groupBy('product.product_id');

    if (!empty($filter->keyword)) {
      $products->where('product_description.title','LIKE','%'. $filter->keyword .'%');
    }

    if (!empty($filter->category_id)) {
      $products->where('product_to_category.category_id','=',$filter->category_id);
    }
    
    if (!empty($filter->range)) {
      $range= explode(',',$filter->range);
      $products->whereBetween('product.price', [$range[0], (!empty($range[1]))? $range[1] : $range[0]]);
    }
    
    return $products->orderBy('product.product_id','DESC')->where('product.status',1)->limit(50);

This query loads in 12.6 seconds. If i remove ->orderBy('product.product_id','DESC'), the query runs in 0.800ms.

In my Database I have index keys on product_description.product_id , product_to_category.product_id and product.id is set to primary

I've seen around that orderBY desc slows down alot with big databases, is there a workaround, i need to order it by DESC because i want the "Latest" uploaded, I've tried setting it on "created_at" column but it's about the same

EDIT

I tried without Laravel, basically the speed is about the same, ordering by DESC slows down the query, is there a solution to this in general or basically DESC is just slow and it should be avoided with BIG databases?

did u try first take your query and after make orderBy is it change anything? — Hasan Çetin, Nov 06 '21 at 16:05
@HasanÇetin After i take my query i only get 50 results, so sorting then with a collection does not do the job for me — Frosty, Nov 07 '21 at 13:02
Did you tried to execute the mysql query manually to see the result ? I think you should add indexes to your table. This is not laravel related at all. — Emil Georgiev, Nov 16 '21 at 00:02
@EmilGeorgiev Yes i did the speed is about the same, ordering by DESC slows down quite alot with and without eloquent — Frosty, Nov 16 '21 at 20:31
Is the 'product.id' column a primary key of the product table? Is joining column 'product.product_id' without an index? What is the relation between the 'product.id' and the 'product.product_id' columns? — David Lukas, Nov 16 '21 at 20:57
What RDBMS vendor and version are you using (assume MySQL)? What is the schema of the table(s)? eg: result of `SHOW CREATE TABLE products`. What is the result of `EXPLAIN` for your query? Lastly, what is the configuration settings for your DB? — Will B., Nov 16 '21 at 21:42
*"ordering by DESC slows down"* Do you mean that ordering by ASC does not? I doubt that. — Olivier, Nov 17 '21 at 08:05
I agree with with above posters that this is not a Laravel issue, but a SQL optimization one. Assuming you are running MySQL, run the query but prepend it with EXPLAIN to show how the query is being handled including it's interactions with your existing indexes. If we can see the explanation for the query, the issue can be debugged further. — Deff Jay, Nov 16 '21 at 21:29
@Olivier Actually that is exactly what I am saying, try it on your own, create a table with 100.000 records, sorting by ASC is about 3-4 faster then ordering by DESC — Frosty, Nov 21 '21 at 16:07

Salman A · Answer 1 · 2021-11-17T11:07:49.750

You are grouping by product.product_id and I am guessing that the select only contains columns from the product table. In that case the left joins can be removed — or — replaced by an exists query. The group by can be removed completely. The joins multiply the rows and group by compacts them; when removed the query becomes simpler and hopefully faster:

select foo, bar, baz
from product
where status = ?
and price between ? and ? -- add this where clause if you need to filter on product price
and exists ( -- add this subquery if you need to filter on description language and/or title
    select 1
    from product_description
    where product_description.product_id = product.product_id
    and product_description.language = ?
    and product_description.title like ?
)
and exists ( -- add this subquery if you need to filter on category id
    select 1
    from product_to_category
    where product_to_category.product_id = product.product_id
    and product_to_category.category_id = ?
)
order by product_id desc
limit 50

Then your group by is wrong (https://stackoverflow.com/q/34115174). You can get random rows from other tables. No point getting wrong results faster. — Salman A, Nov 17 '21 at 21:57

score 1 · Accepted Answer · answered Nov 17 '21 at 13:05

1

I think you can define DESC index, as default index on column is ASC

for example:

CREATE INDEX product_index ON product (product_id DESC);

more information

answered Nov 17 '21 at 13:05

Abilogos

4,777
2
19
39

2

Be advised that it only seems to work in MySQL 8 or later. In older versions, DESC keyword is simply ignored. – Salman A Nov 17 '21 at 22:09
A normal index can be used backwards, so this "solution" should not change anything. – Olivier Nov 18 '21 at 08:16

Laravel Query Builder orderBy id DESC is very slow on 100k rows

2 Answers2