Force a Composite Index When the Where Clause Contains shipping_at_gmt and shipping_status|shipping_type|operated_source

1. Reference: Optimization of list interface response timeout

2. Reference: Forcing the use of an index in query SQL within Spatie\QueryBuilder

3. However, when the fields in the where clause include shipping_at_gmt along with shipping_status and operator_user_id, forcing the use of a composite index results in negative optimization. The SQL execution times are summarized as follows:

SQL

#################################
# 耗时 24 秒
select
  count(*) as aggregate
from
  order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
  `order_shipping_logs`.`operator_user_id` in (20000463)
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 03:27:52'
  and `shipping_at_gmt` <= '2024-10-23 03:27:52';

# 耗时 29 秒
select
  `order_shipping_logs`.*
from
  order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
  `order_shipping_logs`.`operator_user_id` in (20000463)
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 03:27:52'
  and `shipping_at_gmt` <= '2024-10-23 03:27:52'
order by
  `operated_at_gmt` desc
limit
  100 offset 0;

# 耗时 1.5 秒
select
  count(*) as aggregate
from
  order_shipping_logs
where
  `order_shipping_logs`.`operator_user_id` in (20000463)
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 03:27:52'
  and `shipping_at_gmt` <= '2024-10-23 03:27:52';

# 耗时 1.4 秒
select
  `order_shipping_logs`.*
from
  order_shipping_logs
where
  `order_shipping_logs`.`operator_user_id` in (20000463)
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 03:27:52'
  and `shipping_at_gmt` <= '2024-10-23 03:27:52'
order by
  `operated_at_gmt` desc
limit
  100 offset 0;

# 以上的 4 条 SQL，强制使用组合索引属于负优化。原因在于 `operator_user_id` in (20000463) 的数据量（2338）很小

#################################
# 耗时 27 秒
select
  count(*) as aggregate
from
  order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
  `order_shipping_logs`.`operator_user_id` in (20000250)
  and `order_shipping_logs`.`shipping_type` = 1
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 03:43:11'
  and `shipping_at_gmt` <= '2024-10-23 03:43:11';

# 耗时 32 秒
select
  `order_shipping_logs`.*
from
  order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
  `order_shipping_logs`.`operator_user_id` in (20000250)
  and `order_shipping_logs`.`shipping_type` = 1
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 03:43:11'
  and `shipping_at_gmt` <= '2024-10-23 03:43:11'
order by
  `operated_at_gmt` desc
limit
  100 offset 0;

# 耗时 102 秒
select
  count(*) as aggregate
from
  order_shipping_logs
where
  `order_shipping_logs`.`operator_user_id` in (20000250)
  and `order_shipping_logs`.`shipping_type` = 1
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 03:43:11'
  and `shipping_at_gmt` <= '2024-10-23 03:43:11';

# 耗时 67 秒
select
  `order_shipping_logs`.*
from
  order_shipping_logs
where
  `order_shipping_logs`.`operator_user_id` in (20000250)
  and `order_shipping_logs`.`shipping_type` = 1
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 03:43:11'
  and `shipping_at_gmt` <= '2024-10-23 03:43:11'
order by
  `operated_at_gmt` desc
limit
  100 offset 0;

# 以上的 4 条 SQL，强制使用组合索引属于正优化。原因在于 `operator_user_id` in (20000463) 的数据量（18060318）很大

#################################
# 耗时 24 秒
select
  count(*) as aggregate
from
  order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
  `order_shipping_logs`.`operator_user_id` in (4)
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 07:28:36'
  and `shipping_at_gmt` <= '2024-10-23 07:28:36';

# 耗时 30 秒
select
  `order_shipping_logs`.*
from
  order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
  `order_shipping_logs`.`operator_user_id` in (4)
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 07:28:36'
  and `shipping_at_gmt` <= '2024-10-23 07:28:36'
order by
  `operated_at_gmt` desc
limit
  100 offset 0;

# 耗时 7 秒
select
  count(*) as aggregate
from
  order_shipping_logs
where
  `order_shipping_logs`.`operator_user_id` in (4)
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 07:28:36'
  and `shipping_at_gmt` <= '2024-10-23 07:28:36';

# 耗时 6 秒
select
  `order_shipping_logs`.*
from
  order_shipping_logs
where
  `order_shipping_logs`.`operator_user_id` in (4)
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 07:28:36'
  and `shipping_at_gmt` <= '2024-10-23 07:28:36'
order by
  `operated_at_gmt` desc
limit
  100 offset 0;

# 以上的 4 条 SQL，强制使用组合索引属于负优化。原因在于 `operator_user_id` in (4) 的数据量（323230）不大

#################################
# 耗时 24 秒
select
  count(*) as aggregate
from
  order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
  `order_shipping_logs`.`operator_user_id` in (4)
  and `order_shipping_logs`.`logistic_channel_id` in (2775)
  and `order_shipping_logs`.`shipping_type` = 1
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 07:40:53'
  and `shipping_at_gmt` <= '2024-10-23 07:40:53';

# 耗时 29 秒
select
  `order_shipping_logs`.*
from
  order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
  `order_shipping_logs`.`operator_user_id` in (4)
  and `order_shipping_logs`.`logistic_channel_id` in (2775)
  and `order_shipping_logs`.`shipping_type` = 1
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 07:40:53'
  and `shipping_at_gmt` <= '2024-10-23 07:40:53'
order by
  `operated_at_gmt` desc
limit
  100 offset 0;

# 耗时 11 秒
select
  count(*) as aggregate
from
  order_shipping_logs
where
  `order_shipping_logs`.`operator_user_id` in (4)
  and `order_shipping_logs`.`logistic_channel_id` in (2775)
  and `order_shipping_logs`.`shipping_type` = 1
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 07:40:53'
  and `shipping_at_gmt` <= '2024-10-23 07:40:53';

# 耗时 10 秒
select
  `order_shipping_logs`.*
from
  order_shipping_logs
where
  `order_shipping_logs`.`operator_user_id` in (4)
  and `order_shipping_logs`.`logistic_channel_id` in (2775)
  and `order_shipping_logs`.`shipping_type` = 1
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 07:40:53'
  and `shipping_at_gmt` <= '2024-10-23 07:40:53'
order by
  `operated_at_gmt` desc
limit
  100 offset 0;

# 以上的 4 条 SQL，强制使用组合索引属于负优化。原因在于 `operator_user_id` in (4) 的数据量（323230）不大

#################################
# 耗时 24 秒
select
  count(*) as aggregate
from
  order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
  `order_shipping_logs`.`operator_user_id` in (20000463)
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 03:27:52'
  and `shipping_at_gmt` <= '2024-10-23 03:27:52';

# 耗时 29 秒
select
  `order_shipping_logs`.*
from
  order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
  `order_shipping_logs`.`operator_user_id` in (20000463)
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 03:27:52'
  and `shipping_at_gmt` <= '2024-10-23 03:27:52'
order by
  `operated_at_gmt` desc
limit
  100 offset 0;

# 耗时 1.5 秒
select
  count(*) as aggregate
from
  order_shipping_logs
where
  `order_shipping_logs`.`operator_user_id` in (20000463)
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 03:27:52'
  and `shipping_at_gmt` <= '2024-10-23 03:27:52';

# 耗时 1.4 秒
select
  `order_shipping_logs`.*
from
  order_shipping_logs
where
  `order_shipping_logs`.`operator_user_id` in (20000463)
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 03:27:52'
  and `shipping_at_gmt` <= '2024-10-23 03:27:52'
order by
  `operated_at_gmt` desc
limit
  100 offset 0;

# 以上的 4 条 SQL，强制使用组合索引属于负优化。原因在于 `operator_user_id` in (20000463) 的数据量（2338）很小

#################################
# 耗时 27 秒
select
  count(*) as aggregate
from
  order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
  `order_shipping_logs`.`operator_user_id` in (20000250)
  and `order_shipping_logs`.`shipping_type` = 1
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 03:43:11'
  and `shipping_at_gmt` <= '2024-10-23 03:43:11';

# 耗时 32 秒
select
  `order_shipping_logs`.*
from
  order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
  `order_shipping_logs`.`operator_user_id` in (20000250)
  and `order_shipping_logs`.`shipping_type` = 1
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 03:43:11'
  and `shipping_at_gmt` <= '2024-10-23 03:43:11'
order by
  `operated_at_gmt` desc
limit
  100 offset 0;

# 耗时 102 秒
select
  count(*) as aggregate
from
  order_shipping_logs
where
  `order_shipping_logs`.`operator_user_id` in (20000250)
  and `order_shipping_logs`.`shipping_type` = 1
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 03:43:11'
  and `shipping_at_gmt` <= '2024-10-23 03:43:11';

# 耗时 67 秒
select
  `order_shipping_logs`.*
from
  order_shipping_logs
where
  `order_shipping_logs`.`operator_user_id` in (20000250)
  and `order_shipping_logs`.`shipping_type` = 1
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 03:43:11'
  and `shipping_at_gmt` <= '2024-10-23 03:43:11'
order by
  `operated_at_gmt` desc
limit
  100 offset 0;

# 以上的 4 条 SQL，强制使用组合索引属于正优化。原因在于 `operator_user_id` in (20000463) 的数据量（18060318）很大

#################################
# 耗时 24 秒
select
  count(*) as aggregate
from
  order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
  `order_shipping_logs`.`operator_user_id` in (4)
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 07:28:36'
  and `shipping_at_gmt` <= '2024-10-23 07:28:36';

# 耗时 30 秒
select
  `order_shipping_logs`.*
from
  order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
  `order_shipping_logs`.`operator_user_id` in (4)
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 07:28:36'
  and `shipping_at_gmt` <= '2024-10-23 07:28:36'
order by
  `operated_at_gmt` desc
limit
  100 offset 0;

# 耗时 7 秒
select
  count(*) as aggregate
from
  order_shipping_logs
where
  `order_shipping_logs`.`operator_user_id` in (4)
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 07:28:36'
  and `shipping_at_gmt` <= '2024-10-23 07:28:36';

# 耗时 6 秒
select
  `order_shipping_logs`.*
from
  order_shipping_logs
where
  `order_shipping_logs`.`operator_user_id` in (4)
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 07:28:36'
  and `shipping_at_gmt` <= '2024-10-23 07:28:36'
order by
  `operated_at_gmt` desc
limit
  100 offset 0;

# 以上的 4 条 SQL，强制使用组合索引属于负优化。原因在于 `operator_user_id` in (4) 的数据量（323230）不大

#################################
# 耗时 24 秒
select
  count(*) as aggregate
from
  order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
  `order_shipping_logs`.`operator_user_id` in (4)
  and `order_shipping_logs`.`logistic_channel_id` in (2775)
  and `order_shipping_logs`.`shipping_type` = 1
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 07:40:53'
  and `shipping_at_gmt` <= '2024-10-23 07:40:53';

# 耗时 29 秒
select
  `order_shipping_logs`.*
from
  order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)
where
  `order_shipping_logs`.`operator_user_id` in (4)
  and `order_shipping_logs`.`logistic_channel_id` in (2775)
  and `order_shipping_logs`.`shipping_type` = 1
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 07:40:53'
  and `shipping_at_gmt` <= '2024-10-23 07:40:53'
order by
  `operated_at_gmt` desc
limit
  100 offset 0;

# 耗时 11 秒
select
  count(*) as aggregate
from
  order_shipping_logs
where
  `order_shipping_logs`.`operator_user_id` in (4)
  and `order_shipping_logs`.`logistic_channel_id` in (2775)
  and `order_shipping_logs`.`shipping_type` = 1
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 07:40:53'
  and `shipping_at_gmt` <= '2024-10-23 07:40:53';

# 耗时 10 秒
select
  `order_shipping_logs`.*
from
  order_shipping_logs
where
  `order_shipping_logs`.`operator_user_id` in (4)
  and `order_shipping_logs`.`logistic_channel_id` in (2775)
  and `order_shipping_logs`.`shipping_type` = 1
  and `order_shipping_logs`.`shipping_status` = 3
  and `shipping_at_gmt` >= '2024-07-23 07:40:53'
  and `shipping_at_gmt` <= '2024-10-23 07:40:53'
order by
  `operated_at_gmt` desc
limit
  100 offset 0;

# 以上的 4 条 SQL，强制使用组合索引属于负优化。原因在于 `operator_user_id` in (4) 的数据量（323230）不大

4. I preliminarily concluded the following rule: if the fields in the where clause include shipping_at_gmt and shipping_status|shipping_type|operated_source, force the use of the composite index; otherwise, do not force it. The scenario involving a massive data volume (18,060,318) for `operator_user_id` in (20000463) does not need to be considered, as such an extreme case does not exist in the production environment. The final implementation is as follows:

PHP

$whereColumns = array_unique(Arr::pluck($builder->getQuery()->wheres, 'column'));
$filteredWhereColumns = Arr::where($whereColumns, function ($value, $key) {
	return $value != 'shipping_at_gmt';
});
// 当筛选字段包含 交运时间 与 交运状态|交运类型|操作来源 时，强制使用组合索引 order_shipping_logs_sag_os_ss_st_index
if (in_array('shipping_at_gmt', $whereColumns) && !empty($filteredWhereColumns) && empty(array_diff($filteredWhereColumns, ['order_shipping_logs.shipping_status', 'order_shipping_logs.shipping_type', 'order_shipping_logs.operated_source']))) {
	$builder->from(DB::raw('order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)'));
}

$whereColumns = array_unique(Arr::pluck($builder->getQuery()->wheres, 'column'));
$filteredWhereColumns = Arr::where($whereColumns, function ($value, $key) {
	return $value != 'shipping_at_gmt';
});
// 当筛选字段包含 交运时间 与 交运状态|交运类型|操作来源 时，强制使用组合索引 order_shipping_logs_sag_os_ss_st_index
if (in_array('shipping_at_gmt', $whereColumns) && !empty($filteredWhereColumns) && empty(array_diff($filteredWhereColumns, ['order_shipping_logs.shipping_status', 'order_shipping_logs.shipping_type', 'order_shipping_logs.operated_source']))) {
	$builder->from(DB::raw('order_shipping_logs FORCE INDEX (order_shipping_logs_sag_os_ss_st_index)'));
}

5. The final implemented rule produces debug output that matches expectations:

Plaintext

Array
(
    [0] => order_shipping_logs.operator_user_id
    [1] => order_shipping_logs.logistic_channel_id
    [2] => order_shipping_logs.shipping_type
    [3] => order_shipping_logs.shipping_status
    [4] => shipping_at_gmt
)
0 Do not force composite index

Array
(
    [0] => order_shipping_logs.shipping_type
    [1] => order_shipping_logs.shipping_status
    [2] => shipping_at_gmt
)
1 Force composite index

Array
(
    [0] => order_shipping_logs.operated_source
    [1] => order_shipping_logs.shipping_type
    [2] => order_shipping_logs.shipping_status
    [3] => shipping_at_gmt
)
1 Force composite index

Array
(
    [0] => order_shipping_logs.shipping_type
    [1] => shipping_at_gmt
)
1 Force composite index

Array
(
    [0] => shipping_at_gmt
)
0 Do not force composite index

Array
(
    [0] => order_shipping_logs.operator_user_id
    [1] => shipping_at_gmt
)
0 Do not force composite index

Array
(
    [0] => order_shipping_logs.shipping_type
    [1] => operated_at_gmt
)
0 Do not force composite index

Array
(
    [0] => order_shipping_logs.operator_user_id
    [1] => order_shipping_logs.logistic_channel_id
    [2] => order_shipping_logs.shipping_type
    [3] => order_shipping_logs.shipping_status
    [4] => shipping_at_gmt
)
0 Do not force composite index

Array
(
    [0] => order_shipping_logs.shipping_type
    [1] => order_shipping_logs.shipping_status
    [2] => shipping_at_gmt
)
1 Force composite index

Array
(
    [0] => order_shipping_logs.operated_source
    [1] => order_shipping_logs.shipping_type
    [2] => order_shipping_logs.shipping_status
    [3] => shipping_at_gmt
)
1 Force composite index

Array
(
    [0] => order_shipping_logs.shipping_type
    [1] => shipping_at_gmt
)
1 Force composite index

Array
(
    [0] => shipping_at_gmt
)
0 Do not force composite index

Array
(
    [0] => order_shipping_logs.operator_user_id
    [1] => shipping_at_gmt
)
0 Do not force composite index

Array
(
    [0] => order_shipping_logs.shipping_type
    [1] => operated_at_gmt
)
0 Do not force composite index

关于作者 · 持续学习与技术实践

我是一名拥有 15 年以上开发经验的技术从业者，自 2013 年起持续运营个人技术博客，记录工作与个人项目中遇到的真实问题，以及解决问题过程中的探索、实践和思考。

除了技术写作，我也在持续开发和维护自己的独立项目，探索新技术和新工具在实际场景中的应用。我始终相信，没有不值得去解决的问题，也没有不值得去学习的技术。

如果这篇文章对你有所帮助，欢迎浏览博客中的其他文章，也欢迎交流技术经验与想法。

关于我 · GitHub · 邮件联系

Force a Composite Index When the Where Clause Contains shipping_at_gmt and shipping_status|shipping_type|operated_source

更多文章

保护机制也会保护错东西：为什么一个 Documentation 能阻塞整页修订？

页面明明是我自己的，为什么不能直接重写？从 /translation/ 改版看 immutable source 与 replacement successor

为什么修完 Reviewer 指出的所有例子，下一轮还是会失败？从 B→revision→B 看同类问题完整性

为下一次 PHP 8.5 升级做准备：从两次验证失败到自动回滚工具完成