使用子查詢更快地獲得50k行-Laravel5.6

下面的查詢是從有 50k 行的表中獲取候選人的詳細資訊。包括職位、地區和就業型別。候選人具有就業型別的基本詳細資訊，作業，地區在另一個具有外鍵關系的表中。

$candidates =  DB::table('candidates')
->join('role_users', function($join){
    $join->on('candidates.user_id', '=', 'role_users.user_id');
    $join->where('role_users.role_id', 6);    
})
->join('candidate_statuses', 'candidates.candidate_status_id', '=', 'candidate_statuses.id')
->join('employment_types', 'candidates.employment_types_id', '=', 'employment_types.id')
->select(
    'candidates.id', 
    'candidates.user_id', 
    'candidates.candidate_code', 
    'candidates.full_name as name', 
    'employment_types.title AS employment_type',
    DB::raw("(SELECT GROUP_CONCAT(candidate_jobs.job_id SEPARATOR ',') FROM candidate_jobs WHERE candidate_jobs.candidate_id = candidates.id) as job_ids"),
    DB::raw("(SELECT GROUP_CONCAT(regions.name SEPARATOR ',') FROM candidate_regions INNER JOIN regions ON regions.id=candidate_regions.region_id WHERE candidate_regions.candidate_id = candidates.id) as regions"),
    'role_users.email',
    'role_users.login_at',
    'candidates.is_deleted')
->where('candidates.candidate_status_id', '!=' , 6)
->where('candidates.is_deleted', $request->is_deleted)
->orderBy('candidates.first_name')
->groupBy('candidates.id')
->paginate(10);
}

select  `candidates`.`id`, `candidates`.`user_id`, `candidates`.`candidate_code`,
        `candidates`.`first_name`,
        `candidates`.`last_name`, `candidates`.`full_name` as `name`,
        `candidates`.`profile_img`, `candidates`.`candidate_status_id`,
        `candidates`.`employment_types_id`,
        `employment_types`.`title` as `employment_type`,
    (
        SELECT  GROUP_CONCAT(candidate_jobs.job_id SEPARATOR ',')
            FROM  candidate_jobs
            WHERE  candidate_jobs.candidate_id = candidates.id
    ) as job_ids,
    (
        SELECT  GROUP_CONCAT(regions.name SEPARATOR ',')
            FROM  candidate_regions
            INNER JOIN  regions  ON regions.id=candidate_regions.region_id
            WHERE  candidate_regions.candidate_id = candidates.id
    ) as regions,
        `candidates`.`formatted_mobile_number`, `candidates`.`place`,
        `candidates`.`post_code`, `role_users`.`email`, `role_users`.`login_at`,
        `role_users`.`email`, `candidates`.`has_access`, `candidates`.`is_deleted`
    from  `candidates`
    inner join  `role_users`  ON `candidates`.`user_id` = `role_users`.`user_id`
      and  `role_users`.`role_id` = ?
    inner join  `candidate_statuses`
        ON `candidates`.`candidate_status_id` = `candidate_statuses`.`id`
    inner join  `employment_types`
        ON `candidates`.`employment_types_id` = `employment_types`.`id`
    where  (`candidates`.`candidate_status_id` in (?))
      and  `candidates`.`candidate_status_id` != ?
      and  `candidates`.`is_deleted` = ?
    group by  `candidates`.`id`
    order by  `candidates`.`first_name` asc 

在我的本地機器上獲得結果需要 2/ 3 秒，但在生產中需要很長時間。有人可以幫忙嗎？

uj5u.com熱心網友回復：

似乎第二部分是不必要的：

           `candidates`.`candidate_status_id` in (?))
      and  `candidates`.`candidate_status_id` != ?

進行這些匹配可避免對結果的額外傳遞

    group by  `first_name`,    `id`
    order by  `first_name` asc, id

可能有用的索引：

candidates:  INDEX(candidate_status_id, is_deleted, first_name, id, user_id)
role_users:  INDEX(user_id,  email, login_at, role_id)
candidate_jobs:  INDEX(candidate_id,  job_id)
candidate_regions:  INDEX(candidate_id, region_id)

標籤：mysql 加入 laravel-5

上一篇：如何連接兩個表以在視圖中獲得輸出

下一篇：JavaScript基礎語法五（條件陳述句）