In MySQL 8.0, there are 10,000 parameters in the in condition, resulting in the analysis of the query time is too long, and the interface response timeout is analyzed.
1. There are 10,000 parameters in the IN condition (the upper limit of the product needs to be 10,000), resulting in a long query time, and the interface response timeout. as shown in Figure 1
{
"filter": {
"plat_order_id": [
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
...,
10000
]
},
"page": 1,
"per_page": 100
}
{
"status_code": 500,
"code": 0,
"message": "Maximum execution time of 300 seconds exceeded",
"trace": {
"line": 420,
"file": "E:\\wwwroot\\erp-backend\\vendor\\laravel\\framework\\src\\Illuminate\\Database\\Connection.php",
"class": "Symfony\\Component\\ErrorHandler\\Error\\FatalError",
"trace": [
"#0 {main}"
]
}
}
2. When the number of parameters of PLAT_ORDER_ID is reduced from 10000 to 5, the interface response does not time out, and the total number of records in the table is 20 million. as shown in Figure 2
3. Generate SQL as follows
select
count(*) as aggregate
from
`orders`
where
`orders`.`plat_order_id` in ('95798');
4. Adjust the SQL, decide to force the use of the index use index, no longer timeout
select
count(*) as aggregate
from
`orders` USE INDEX(`orders_plat_order_id_index`)
where
`orders`.`plat_order_id` in ('95798')
