Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "how to force laravel to specify an index for query". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Why do you need to force indexing?

The database does not use the index we envisioned for sql queries, resulting in a particularly slow query.

Mysql mandatory index query statement

Select * from user where age = 26 force index (age); / / mandatory indexing

Select * from user where age = 26 use index (age); / / priority to search according to this index

/ * detect whether an index exists in a table * @ param $table * @ param $index * @ return bool * @ author zhaohao * @ date 2019-08-26 17:42 * / if (! function_exists ('hasIndex')) {function hasIndex ($table, $name) {$conn = IlluminateSupportFacadesSchema::getConnection (); $dbSchemaManager = $conn- > getDoctrineSchemaManager (); $doctrineTable = $dbSchemaManager- > listTableDetails ($table); return $doctrineTable- > hasIndex ($name) }}

You need to write this in the laravel code:

Here, the when method is used to determine whether the index exists. If the index does not exist, the index will not be used, otherwise it will report an error and avoid errors caused by someone mistakenly deleting the index.

The statement to force the index here is:

-> from (DB::raw ('`erp_ agents` FORCE INDEX (`test`)

For example:

$agents = Agent::where ($whereType)-> when (hasIndex ('Agent',' test'), function ($Q) {$Q-> from (DB::raw ('erp_ agents` FORCE INDEX (`test`));})-> when (request (' position',false), function ($Q) {$Q-> whereIn ('position_id',request (' position') })-> whereIn ('agents.status', $validStatus)-> where (' worked_at','

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.