MySQL Troubleshooting

Localizing the Problem (Minimizing the Test Case)

I already showed the value of minimizing a test case in Single Server Example, where I reduced an incorrect SELECT to a CREATE TABLE that reproduced the wrong parameters. Here I will discuss the principle of a minimal test case in more general terms.

As an example, take the following huge query^[22]:

SELECT IF(TABLE1.FIELD1 = 'R' AND TABLE1.FIELD2 IS NOT NULL AND TABLE1.FIELD3 = '1' AND TABLE2.FIELD4 = TABLE2.FIELD5 AND TABLE3.FIELD6 = TABLE4.FIELD6, TABLE3.FIELD7, TABLE4.FIELD7) AS ALIAS1, IF(TABLE1.FIELD1 = 'R' AND TABLE1.FIELD2 IS NOT NULL AND TABLE1.FIELD3 = '1' AND TABLE2.FIELD4 = TABLE2.FIELD5 AND TABLE3.FIELD6 = TABLE4.FIELD6, TABLE3.FIELD8, TABLE4.FIELD8) AS ALIAS2, SUM( IF ( (SELECT TABLE5.FIELD7 FROM TABLE4 ALIAS3, TABLE2 ALIAS4, TABLE4 ALIAS5 WHERE TABLE5.FIELD5 = ALIAS4.FIELD4 AND ALIAS4.FIELD5 = ALIAS5.FIELD5 AND ALIAS5.FIELD7 = FIELD9 AND TABLE5.FIELD6 = TABLE6.FIELD7 LIMIT 1 ) IS NULL, 0, TABLE7.FIELD10/TABLE7.FIELD11) ) AS ALIAS11 FROM TABLE4 ,TABLE4 ALIAS6, TABLE8 , TABLE4 ALIAS7, TABLE9 , TABLE7 , TABLE2 , TABLE1 FORCE INDEX(FIELD12) LEFT JOIN TABLE1 ALIAS8 ON TABLE1.FIELD13 = TABLE10.FIELD13 LEFT JOIN TABLE1 ALIAS9 ON ALIAS9.FIELD13 = TABLE10.FIELD2 LEFT JOIN TABLE4 ALIAS10 ON ALIAS10.DFIELD5 = TABLE3.FIELD5 WHERE TABLE1.FIELD14 > DATE_sub(now(), INTERVAL 16 DAY) and TABLE1.FIELD1 IN ('P', 'R','D') AND TABLE1.DFIELD5 = TABLE4.FIELD5 AND TABLE1.FIELD15 = TABLE8.FIELD16 AND TABLE6.FIELD7 = TABLE8.FIELD17 AND TABLE4.FIELD18 ...