Level 102
Do you experience the same if you run the queries in your database editor? Or only in laravel?
Jul 22, 2022
17
Level 1
DESC orderBy taking longer time than orderBy ASC
I tried using the same eloquent query for a specific field to be both ASC and DESC. It takes 120ms with desc and only 40ms without desc. What could be the solution? I have tried indexing schedule_time with desc through SQL. Does not seem to help. The query is as follows:
Post::with(['categories' => function($query) use ($category) {
$query->where('categories.id', $category->id);
}])->where('schedule_time', '<=', date('Y-m-d H:i:s'))
->where('is_active', true)
->orderBy('order_no)
->orderBy('schedule_time', 'desc')
->limit(25)
->get();
Level 1
@Sinnbeck Only with laravel
Level 102
@parash75 and you are sure it's the query and not the request? How are you checking?
Level 1
@Sinnbeck I am checking it through laravel telescope
Level 102
@parash75 any change if you don't eager load? And perhaps verify with debugbar
Level 1
@Sinnbeck Is there a way to optimize this query? Actually I have around 50k data in production and a threshold for API endpoint to be loaded within a few milliseconds. And I am not able to achieve that. Currently I am with the query below:
Category::with(['posts' => function($query) {
$query
->where('schedule_time', '<=', date('Y-m-d H:i:s'))
->where('is_active', true)
->orderBy('order_no')
->orderBy('schedule_time', 'desc')
->limit(25);
}])
->where('slug', $slug)->where('is_active', true)->first();
Also what should be a good response time for the query?
Level 102
@parash75 did you try adding indexes on the columns you filter by? I suggest seeding around the same amount of data locally so that you can test it out. Personally I tweak the table locally in my database editor and run the query there while testing. Once it's performance is as expected, I write the changes as a migration
Level 1
@Sinnbeck I have tried indexing. Category and Post have many-to-many relationship. I receive category_id in my endpoint to which I need to respond with 25 posts with given conditions. I have a pivot table for them. I am not sure how to optimize it.
Level 102
@parash75 those are different queries. Laravel does seperate queries for loading relationships. I assume those few milli seconds are for just a single query?
Level 1
@Sinnbeck It is 500ms to be precise. If the client does not receive response within 500ms, it skips the endpoint. Similarly, if it needs to skip the endpoint for a few more time, the client automatically blacklists the endpoint.
Level 102
@parash75 ah ok. So 500ms in total. I would think that should be achievable. But go over each query laravel makes and optimize it as much as possible. I personally use debugbar to get an overview, and then take one query at a time and optimize outside of laravel
1 like
Level 1
@Sinnbeck I will try that. Thank you.
Level 35
Do you have a database index on both order_no and schedule_time?
If I'm not mistaken the index of mysql has an influence and you have two orderBy records. One sorting ASC and one DESC. There is quite some documentation on it at:
- https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html
- https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html
However, adding indexes also have a negative impact on insert queries as it takes a bit of time to update indexes after each insert. But it might be worth a try to create an index of the combination order_no + schedule_time in the order you want and see if that helps.
Level 1
@click Yes both of them do have indexes
Level 35
@parash75 Try creating an index INDEX order_no_schedule_time_desc (order_no ASC, schedule_time DESC), it might help. But take into account that each index also has to be stored somewhere and updating it takes a bit of time.
1 like
Level 1
@click Wow I really appreciate it since this has shown visual changes. But how do we index desc through migration in laravel? I use continuous deployment
Level 35
@parash75 You can always run a custom statement to alter the table with \DB::statement('ALTER TABLE .... '); in your migration file
1 like
Please or to participate in this conversation.