MySQL Query on bad designed table with multiple Selfjoins

join;MySQL

i have to create a report out of a bad designed database (I cannot change)

It saves different Formulars into a form_values table

---+-------+-----------+-------
id | value | form_item | entry 
---+-------+-----------+-------
  • value = input value (DB type = text but can also hold numeric inputs dates and so on)
  • form_item = input name (example: "name:", "birthday:")
  • entry = id to formular

My Problem on big Formulars I have to self join multiple times to get all form values and make conditions out of it

Example query:

    SELECT fv.entry as 'entry' FROM form_values fv
                INNER JOIN form_entry_links fel ON fel.child = fv.entry
                LEFT JOIN form_values fv2 ON fv2.entry = fel.child AND fv2.form_item = 117
                LEFT JOIN form_values fv3 ON fv3.entry = fel.child AND fv3.form_item = 118
                LEFT JOIN form_values fv4 ON fv4.entry = fel.child AND fv4.form_item = 119
                LEFT JOIN form_values fv5 ON fv5.entry = fel.child AND fv5.form_item = 120
                LEFT JOIN form_values fv6 ON fv6.entry = fel.child AND fv6.form_item = 121
                LEFT JOIN form_values fv7 ON fv7.entry = fel.child AND fv7.form_item = 122
                LEFT JOIN form_values fv8 ON fv8.entry = fel.child AND fv8.form_item = 123
                LEFT JOIN form_values fv9 ON fv9.entry = fel.child AND fv9.form_item = 124
                LEFT JOIN form_values fv10 ON fv10.entry = fel.child AND fv10.form_item = 125
                LEFT JOIN form_values fv11 ON fv11.entry = fel.child AND fv11.form_item = 126
                LEFT JOIN form_values fv12 ON fv12.entry = fel.child AND fv12.form_item = 127
                LEFT JOIN form_values fv13 ON fv13.entry = fel.child AND fv13.form_item = 128
                LEFT JOIN form_values fv14 ON fv14.entry = fel.child AND fv14.form_item = 129
                LEFT JOIN form_values fv15 ON fv15.entry = fel.child AND fv15.form_item = 132
                WHERE
                fv.entry IN(4,5,6,10,11,12,16,17,18,22,23,24,28,29,30,34,35,38,41,43,45,48,51,52,54,55,57,58,60,61,63,64,65,67,68,70,72,75,78,79,82,83)
                AND fv.entry IN (43,48,51,52,54,55,57,58,60,61,63,64,65,67,68,70,75,82,4,5,6,10,11,12,16,17,18,22,23,24,28,29,30,34,35,38,78,79)
                AND fv.entry IN (4,5,6,10,11,12,16,17,18,22,23,24,28,29,30,34,35,38,78,79)
                AND fv2.value = 0
                AND fv3.value = 0
                AND fv4.value = 0
                AND fv5.value = 0
                AND fv6.value = 0
                AND (
                    ( fv2.value > 0)
                    OR( fv7.value > 0)
                    OR( fv8.value > 0)
                    OR( fv9.value > 0)
                    OR( fv10.value > 0)
                    OR( fv11.value > 0)
                    OR( fv12.value > 0)
                    OR( fv13.value > 0)
                    OR( fv14.value > 0)
                    OR( fv15.value > 0)
                )
                 GROUP BY fv.entry

This is really slow and so the report takes over 1 hour on a small database with 15000 entries

Maybe someone can help me to make a performant query out of this mess,
thank you so much

Best Answer

Looking at your Query, I have a few suggestions:

  1. Get rid of the "OR"s. you can do this by checking if the addition of all the fields > 0... OR's are a killer.
  2. as Jkavalik mentioned, use a distinct instead of a group by... better perf.
  3. The "AND"s of Values 2 to 6 can also be an addition = 0
  4. Do you absolutely need the Left joins? Can they be replaced with inner joins?

As for the logic, I'm not sure I understand the whole concept.

Related Question