【宋词】《永遇乐》 辛弃疾：千古江山，英雄无觅，孙仲谋处。舞榭歌台，风流总被，雨打风吹去。斜阳草树，寻常巷陌，人道寄奴曾住。想当年，金戈铁马，

永夜

没有不值得去解决的问题，也没有不值得去学习的技术！

Force an Index on SQL Queries in Spatie\QueryBuilder

The generated SQL is as follows, which meets expectations

作者：

永夜

在

, , , , , ,
Views: 81

1. Reference: Optimization of list interface response timeout. Forcing the query SQL to use an index in Spatie\QueryBuilder. The implementation is as follows:

PHP
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, which meets expectations. As shown in Figure 1:

The generated SQL is as follows, which meets expectations
SQL
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'

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

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

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

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

关于我  ·  GitHub  ·  邮件联系

更多文章