Get 50k rows faster with subqueries - Laravel 5.6-CodePudding

The below query is to get the candidate's details from the table which has 50k rows. Including jobs, regions, and employment types. A candidate has basic details with employment type, jobs, regions are in another table with foreign key relation.

$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

It takes 2/ 3 seconds to get the result in my local machine but in production takes too long time. Can anyone please help?

CodePudding user response：

It seems like the second part is unnecessary:

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

Making these match avoids an extra pass over the results

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

Possibly helpful indexes:

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)