Get 50k rows faster with subqueries – Laravel 5.6

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0