In Spatie\QueryBuilder, query SQL enforces index
1. Reference:Optimization of List Interface Response Timeout . In Spatie\QueryBuilder , query SQL enforces index . achieve the following
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Arr;
// 当筛选字段包含：交运状态、交运类型、操作来源 时，强制使用组合索引 order_shipping_logs_sag_os_ss_st_index
if (Arr::hasAny($criteria['filter'], ['shipping_status','shipping_type', 'operated_source'])) {
Log::info(
'$criteria1',
$criteria['filter']
);
$builder->from(DB::raw('order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)'));
}
2. The generated sql is as follows, in line with expectations. as shown in Figure 1
select
count(*) as aggregate
from
order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
`order_shipping_logs`.`shipping_type` = 2
and `shipping_at_gmt` >= '2024-07-22 03:39:54'
and `shipping_at_gmt` <= '2024-10-22 03:39:54';
select
count(*) as aggregate
from
order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
`order_shipping_logs`.`operated_source` in (3)
and `order_shipping_logs`.`shipping_type` = 2
and `order_shipping_logs`.`shipping_status` = 3
and `shipping_at_gmt` >= '2024-07-22 03:47:16'
and `shipping_at_gmt` <= '2024-10-22 03:47:16'
