StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Alright, third time is the charm:</p> <p><strong>First</strong>, your performance problem comes from the database structure, not the query.</p> <p>You need to add the following indexes to get a serious performance boost:</p> <pre><code>ALTER TABLE `candidate_region` ADD INDEX `REGION_ID` ( `region_id` ) ALTER TABLE `candidate_region` ADD INDEX `CANDIDATE_ID` ( `candidate_id` ) ALTER TABLE `candidate_job` ADD INDEX `JOB_ID` ( `job_id` ) ALTER TABLE `candidate_job` ADD INDEX `CANDIDATE_ID` ( `candidate_id` ) </code></pre> <p>Pivot tables with the proper indexes work better.</p> <p><strong>Second</strong>, THIS is the (pure) SQL query you want to run:</p> <pre><code>SELECT * FROM candidates INNER JOIN candidate_region ON candidates.id = candidate_region.candidate_id INNER JOIN candidate_job ON candidates.id = candidate_job.candidate_id WHERE imavailable = 1 AND dateDisponible <= '2013-12-31' AND region_id IN (2,3,4,43,42) AND job_id IN (1,2,5,8) </code></pre> <p>With the indexes above, this query runs under a second. Without the indexes, it timed out on my machine.</p> <p><strong>Third</strong>, this is what this query should look like in Fluent:</p> <pre><code>DB::table('candidates') ->join('candidate_region', 'candidates.id', '=', 'candidate_region.candidate_id'); ->join('candidate_job', 'candidates.id', '=', 'candidate_job.candidate_id'); ->whereIn('candidate_region.region_id',$inputs['region']) ->whereIn('candidate_job.job_id',$inputs['job']) ->where('imavailable', '1') ->where('dateDisponible', '<=', $inputs['availableDate']) ->get(); // Or paginate() </code></pre> <p>This is untested, but should work as-is or with minor modifications.</p> <p>Enjoy !</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload