Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is actually a very good developer question that others may need to know about to circumvent limitations placed by hosting companies or senior DBAs. +1 on this question !!!
–
RolandoMySQLDBAMay 25 '11 at 12:59

You need to rafactor the query in such a way that you control and micromanage the temp tables being created and their sizes. Based solely on the JOIN, WHERE, and GROUP BY clauses, you need to implement the following changes:

jobs needs to be indexed on job_visibility_id,active,id

Needed Subquery

(SELECT id job_id FROM jobs WHERE job_visibility_id=1 AND active=1 ORDER BY id)

skills needs to be indexed on valid,id

Needed Subquery

(SELECT id skill_id FROM skills WHERE valid=1 ORDER BY id)

jobs_skills needs to be indexed on skill_id,job_id

Needed Subquery

(SELECT job_id FROM jobs_skills WHERE skill_id IN (96,101,103,108,121,2610,99,119,2607,102,104,112,113,122,1032,1488,2608,109,126,1438,2310,2318,2622,118,1046,1387,2609,100,116,123,2611,2612,2616,2618,114,127,1562,1587,1608,2276,2615,125,1070,1071,1161,1658,2613,2614,2617,105,110,111,120,1394,1435) ORDER BY skill_id,job_id)

SELECT skill_id
FROM (SELECT JS.*
FROM (SELECT skill_id,job_id FROM jobs_skills WHERE skill_id IN (96,101,103,108,121,2610,99,119,2607,102,104,112,113,122,1032,1488,2608,109,126,1438,2310,2318,2622,118,1046,1387,2609,100,116,123,2611,2612,2616,2618,114,127,1562,1587,1608,2276,2615,125,1070,1071,1161,1658,2613,2614,2617,105,110,111,120,1394,1435) ORDER BY skill_id,job_id) JS
INNER JOIN
(SELECT id job_id FROM jobs WHERE job_visibility_id=1 AND active=1 ORDER BY id) J
USING (job_id) INNER JOIN
(SELECT id skill_id FROM skills WHERE valid=1 ORDER BY id) S USING (skill_id)
) A
GROUP BY job_id;

fantastic - I can see the underlying method of creating these indexes/subqueries & can apply it to the other problem-causing queries. Thanks!
–
JIStoneMay 24 '11 at 19:38

@RolandMySQLDBA 'Now combine the Subqueries to form VOLTRON' lol... Thanks for the humor. Made my day...
–
StanleyJohnsMay 24 '11 at 20:13

I think if you had just forced the query to use the PRIMARY index for the jobs table, that would have helped as well, JOIN jobs FORCE INDEX(PRIMARY), because as evident from the EXPLAIN output, the problem lies in the fact that MySQL is not choosing the optimal index when filtering rows from jobs table. You can also use STRAIGHT_JOIN to force MySQL to follow your join plan,.
–
ovais.tariqMay 25 '11 at 11:52

@ovais.tariq FORCE INDEX does not always help in most instances because the MySQL Query Optimizer has this filthy habit of optimizing away index hints due to making rows, suggestions, and even LIMIT clauses vanish into thin air (dba.stackexchange.com/questions/1371/…). The benefits of FORCE INDEX also get clobbered when JOIN clauses are done first and then WHERE clauses are applied to huge temp tables (which are never indexed).
–
RolandoMySQLDBAMay 25 '11 at 12:57

@RolandMySQLDBA., yes joins are done first and in the order left to right., but indexes help the optimizer select the right table first,. Suppose you have 3 tables, t1, t2 and t3,. they would be joined in order t3xt2xt1, if t3 can be filtered by the index to have the least number of rows,. So by using force index you are actually making sure that the optimizer is joining left to right in an optimized way,.
–
ovais.tariqMay 30 '11 at 8:28