Home > PHP Framework > Laravel > Laravel learning records force specified index for query

Laravel learning records force specified index for query

藏色散人
Release: 2021-11-05 14:20:45
forward
2739 people have browsed it

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);
    }
}
Copy after login
  • 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`)'))
Copy after login

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', '<=', $end)
            ->where('is_suppose', 0)
            ->addDomination('m.statistics-human-view')
            ->leftJoin('positions', 'positions.id', '=', 'agents.position_id')
            ->get(['worked_days', 'worked_at']);
Copy after login

[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!

Related labels:
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Issues
Composer cannot install laravel
From 1970-01-01 08:00:00
0
0
0
Laravel Space/laravel-backup cannot be installed
From 1970-01-01 08:00:00
0
0
0
Laravel 5.1 Login laravel comes with it No more
From 1970-01-01 08:00:00
0
0
0
Why thinkphp has better performance than laravel?
From 1970-01-01 08:00:00
0
0
0
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template