Timeline for Dealing with temp tables when I have not control over db variables
Current License: CC BY-SA 3.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jun 3, 2011 at 12:42 | comment | added | RolandoMySQLDBA | @tariq The Query Optimizer will sometimes optimize the FORCE INDEX away if the cardinality of the index columns are lopsided. In principle using FORCE INDEX is just straight up bad practice due to very bad data organization. Think about it: Should you ever have to coerce mysql to perform a FORCE INEX on a Primary Key? This is a strong indication that either the primary key choice was just bad or that mysql designed FORCE INDEX horrifically. | |
| Jun 3, 2011 at 11:58 | comment | added | ovais.tariq | @RolandMySQLDBA,., there is yet another thing that I forgot to mention, optimizer depends a lot on the underlying table engine, because its the underlying table engine that reports the index statistics that are then used by the optimizer to create a query plan,. and InnoDB is different in that the statistics that are generated are not persistent,. so for suppose there are two indexes that could fulfill a query condition, then innodb would have to do index dives for both the indexes to tell the optimizer which one is better, in such cases FORCE INDEX would prevent those index dives,. | |
| May 30, 2011 at 8:28 | comment | added | ovais.tariq | @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,. | |
| May 25, 2011 at 12:57 | comment | added | RolandoMySQLDBA | @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). | |
| May 25, 2011 at 11:52 | comment | added | ovais.tariq | 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,. | |
| May 24, 2011 at 20:13 | comment | added | StanleyJohns | @RolandMySQLDBA 'Now combine the Subqueries to form VOLTRON' lol... Thanks for the humor. Made my day... | |
| May 24, 2011 at 19:39 | vote | accept | JIStone | ||
| May 24, 2011 at 19:38 | comment | added | JIStone | fantastic - I can see the underlying method of creating these indexes/subqueries & can apply it to the other problem-causing queries. Thanks! | |
| May 24, 2011 at 18:49 | history | answered | RolandoMySQLDBA | CC BY-SA 3.0 |