Laravel learning records force specified index for query

                                                                                                               

Why does it need to be forced to index?

The database does not use the index we imagined for SQL query, resulting in extremely slow query.

mysql force index query statement

• select * from user where age = 26 force index(age); / / Force index

• select * from user where age = 26 use index(age); // Prioritize searching based on this index

/**
 * 检测某个表中是否存在某个索引
 * @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 laravel code:

Use the when method here to determine whether the index exists. If it does not exist, do not use this. Index, otherwise an error will be reported to prevent someone from accidentally deleting the index, causing the system to report an error.

The mandatory index statement 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(&#39;worked_at&#39;, &#39;<=&#39;, $end)
            ->where(&#39;is_suppose&#39;, 0)
            ->addDomination(&#39;m.statistics-human-view&#39;)
            ->leftJoin(&#39;positions&#39;, &#39;positions.id&#39;, &#39;=&#39;, &#39;agents.position_id&#39;)
            ->get([&#39;worked_days&#39;, &#39;worked_at&#39;]);

[Related recommendations: The latest five Laravel video tutorials]

The above is the detailed content of Laravel learning records force specified index for query. For more information, please follow other related articles on the PHP Chinese website!