Query takes longer with mariadb 10 vs mysql 5.6-CodePudding

We have recently migrated a PHP web application to a different hosting. Along with that, the database was also successfully copied over to the new mysql server. The earlier version of database server was mysql5.6 and the new hosting has mariadb 10 installed. Below is one example query which is on old hosting is taking around 0.5s and on the new hosting with mariadb, it is taking around 40s which is very long. This long time is chocking up the hosting server.

            SELECT 
                IB.IndicatorId, 
                IB.IndicatorTitle,
                IB.IndicatorCode,
                IC.IndicatorCategoryName,
                AA.Answer,
                GROUP_CONCAT(AI.`Answer`) AS CombinedAnswers
            FROM answer AS A
            LEFT JOIN answers_answer AS AA ON AA.AnswerId = A.AnswerId
            LEFT JOIN activity_sections_indicators AS ASI ON ASI.SectionIndicatorId = AA.SectionIndicatorId
            LEFT JOIN indicators_bank AS IB ON IB.IndicatorId = ASI.IndicatorId
            LEFT JOIN indicator_categories AS IC ON IC.IndicatorCategoryId = IB.IndicatorCategoryId
            LEFT JOIN answers_items AS AI ON AI.AnswerAnswerId = AA.AnswerAnswerId
            WHERE A.ProgramActivityId IN (103,104,105) 
            AND A.Code = 'Newsas263'
            GROUP BY IB.IndicatorId

Further more the number of records in tables is as follows

answer : 5355

answers_answer : 845209

activity_sections_indicators : 2866

indicators_bank : 1175

indicator_categories : 17

answers_items : 934347

My understanding is that there is something missing in mariadb server configuration though the query can be optimized as well. I have VPS and can change configuration for mariadb but I am not sure what to do from here.

Let me know if some more detail is needed. I really appreciate your help in this regard.

CodePudding user response：

MySQL and MariaDB diverged somewhat significantly with 5.6. It would be hard to spot the particular thing that is different.

The EXPLAIN SELECT ... output might help.

These composite and/or covering indexes may help on both servers:

IB:  INDEX(IndicatorId,  IndicatorTitle, IndicatorCode, IndicatorCategoryId)
IC:  INDEX(IndicatorCategoryId,  IndicatorCategoryName)
AA:  INDEX(AnswerId,  Answer, SectionIndicatorId, AnswerAnswerId)
AI:  INDEX(AnswerAnswerId,  Answer)
A:   INDEX(Code, ProgramActivityId, AnswerId)
ASI: INDEX(SectionIndicatorId,  IndicatorId)

When adding a composite index, DROP index(es) with the same leading columns. That is, when you have both INDEX(a) and INDEX(a,b), toss the former.

The index on "A" may be the most important.

Are any of the tables "many-to-many"?

Which version of MariaDB? There is already 10.0 through 10.7.

CodePudding user response：

I was able to resolve the issue.

Thanks for the recommendation/suggestion by "Ergest Basha", "Markus Zeller" and "Rick James".

I ran the "Explain Select ..." on the query and the "Execution Plan" has some differences. I was able to mitigate those differences and the query on new server now works like normal.

Though I was not clearly able to find out the differences but I just re copied the whole database and the issue was resolved.