Filters query take a long time

ThanhSonITNIC commented 5 months ago

Laravel Rest Api Version

2.4

Laravel Version

10

PHP Version

8.1

Database Driver & Version

mysql

Description

Filters query take a long time in both local and server

Steps To Reproduce

Search with filters [POST] /api/products/search

{
"search": {
"filters": [
    {
        "field": "id",
        "operator": "in",
        "value": ["1", "2"]
    }
]
}
}


[2024-01-22 03:09:08] local.DEBUG: select * from `personal_access_tokens` where `personal_access_tokens`.`id` = ? limit 1 {"bindings":["1"],"time":2.84} 
[2024-01-22 03:09:08] local.DEBUG: select * from `users` where `users`.`id` = ? limit 1 {"bindings":["1"],"time":0.54} 
[2024-01-22 03:09:08] local.DEBUG: update `personal_access_tokens` set `last_used_at` = ?, `personal_access_tokens`.`updated_at` = ? where `id` = ? {"bindings":["2024-01-22 03:09:08","2024-01-22 03:09:08",1],"time":2.59}

take 20s here

[2024-01-22 03:09:28] local.DEBUG: select count() as aggregate from products where seller_id = ? and (products.id in (?, ?)) {"bindings":["1","1","2"],"time":1.99} [2024-01-22 03:09:28] local.DEBUG: select from products where seller_id = ? and (products.id in (?, ?)) order by products.id desc limit 50 offset 0 {"bindings":["1","1","2"],"time":0.76}


- Search without filters `[POST] /api/products/search`

[2024-01-22 03:10:53] local.DEBUG: select from personal_access_tokens where personal_access_tokens.id = ? limit 1 {"bindings":["1"],"time":20.95} [2024-01-22 03:10:53] local.DEBUG: select from users where users.id = ? limit 1 {"bindings":["1"],"time":0.66} [2024-01-22 03:10:53] local.DEBUG: update personal_access_tokens set last_used_at = ?, personal_access_tokens.updated_at = ? where id = ? {"bindings":["2024-01-22 03:10:53","2024-01-22 03:10:53",1],"time":3.55}

take 1s here

[2024-01-22 03:10:54] local.DEBUG: select count() as aggregate from products where seller_id = ? {"bindings":["1"],"time":0.71} [2024-01-22 03:10:54] local.DEBUG: select from products where seller_id = ? order by products.id desc limit 50 offset 0 {"bindings":["1"],"time":0.66}

ThanhSonITNIC commented 5 months ago

after debug, i found this problem https://github.com/Lomkit/laravel-rest-api/blob/3e7667ef7dd84350931f02aebb597ced1c184fe4/src/Rules/SearchRules.php#L99-L143 this is a lot of data with size 92MB

ThanhSonITNIC commented 5 months ago

https://github.com/Lomkit/laravel-rest-api/pull/100

GautierDele commented 5 months ago

Hello @ThanhSonITNIC,

Thanks for your contribution, if I understand well, in your case the problem is that the fields in the rules are taking a lot of memory ? How much resources do you have in your project ? I'll investigate also because a few weeks ago I decided to be aware about nested fields because in other places it took times to calculate 😄

ThanhSonITNIC commented 5 months ago

I have 24 resources, each resource only 2 to 5 relations, and around 10 fields each resource this function generate many nested relation fields https://github.com/Lomkit/laravel-rest-api/blob/3e7667ef7dd84350931f02aebb597ced1c184fe4/src/Rules/SearchRules.php#L114 in some resources it throw error memory size exhausted this pull request https://github.com/Lomkit/laravel-rest-api/pull/100 can limit useable fields

GautierDele commented 5 months ago

@ThanhSonITNIC I can confirm on my side on a large project i went from 3.5 sec to 300ms on a search

Lomkit / laravel-rest-api