1

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

4
  • 2
    What is the GROUP BY for? Do you maybe want SELECT DISTINCT fv.entry as 'entry' ... ? Please add the EXPLAIN output for this query and the structure of form_entry_links and form_values in the create table form. – jkavalik Feb 16 '16 at 13:18
  • Note: Having AND over 3 fv.entry IN(...) will merge them, so just use one fv.entry IN(/*same values in all*/) ;). – shA.t Feb 16 '16 at 13:46
  • Thanks i will give your Examples a try ! and add an example output asap – M41DZ3N Feb 16 '16 at 14:15
  • EAV schema design sucks; you are finding one reason why. – Rick James Feb 17 '16 at 6:36
0

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.

4
  • INNER JOIN takes more time for some reason so i used a left JOIN Thanks for your help i will give it a try – M41DZ3N Feb 16 '16 at 15:12
  • @M41DZ3N inner join should take less time. Make sure you have indexes on the fields used in the joins and the wheres... Not sure if it's still the case but in mysql 5.1 that I used to work with, up to 2 indexes were used per query. – JohnG Feb 16 '16 at 18:47
  • @M41DZ3N also just noticed that if you have Left joins, values can be null... remember that NULL is never = to anything and never <> than anything. you have to put IFNULLs everywhere a field from a left join is used. – JohnG Feb 16 '16 at 19:04
  • I changed it to an INNER JOIN form_values fv2 ON fv2.entry = fel.child AND fv2.form_item IN (...) .... HAVING SUM(fv2.value) > 0. This improved it a lot thanks to everyone – M41DZ3N Feb 19 '16 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.