After the datacenter updated my server with 10.3.25 MariaDB I am having issues with a php script running extremely slow and the datacenter techs don't know how to fix. I had a programmer check and he reported the following:
hello, I put some debuging to see which requests are slow
This one takes 60 seconds : SELECT wmsurvey_company_coach.username, qrycompanysurveys.status, Count(qrycompanysurveys.companysurveyid) AS status_count FROM wmsurvey_company_coach INNER JOIN qrycompanysurveys ON wmsurvey_company_coach.companyid = qrycompanysurveys.companyid GROUP BY wmsurvey_company_coach.username, qrycompanysurveys.status ORDER BY username;
this one 170 
seconds
SELECT * FROM qrysurveydashboard ORDER BY name, companyname, status |
it is a view, which translate to the following query :
select `qrycompanysurveys`.`companysurveyid` AS `companysurveyid`,`tntadven_wmsurvey`.`wmsurvey_company_coach`.`username` AS `username`,`tntadven_wmsurvey`.`wmsurvey_users`.`name` AS `name`,`tntadven_wmsurvey`.`wmsurvey_companies`.`companyname` AS `companyname`,`tntadven_wmsurvey`.`wmsurvey_surveys`.`name` AS `surveyname`,`qrycompanysurveys`.`status` AS `status`,if((`qrycompanysurveys`.`status` = 'Preparing'),`qrystatusnotes`.`preparing_note`,if((`qrycompanysurveys`.`status` = 'Underway'),`qrystatusnotes`.`underway_note`,if((`qrycompanysurveys`.`status` = 'Completed'),`qrystatusnotes`.`completed_note`,''))) AS `notes` from (`tntadven_wmsurvey`.`wmsurvey_users` join (`tntadven_wmsurvey`.`qrystatusnotes` join (`tntadven_wmsurvey`.`wmsurvey_company_coach` join ((`tntadven_wmsurvey`.`qrycompanysurveys` join `tntadven_wmsurvey`.`wmsurvey_companies` on((`qrycompanysurveys`.`companyid` = `tntadven_wmsurvey`.`wmsurvey_companies`.`companyid`))) join `tntadven_wmsurvey`.`wmsurvey_surveys` on((`qrycompanysurveys`.`surveyid` = `tntadven_wmsurvey`.`wmsurvey_surveys`.`surveyid`))) on((`tntadven_wmsurvey`.`wmsurvey_company_coach`.`companyid` = `qrycompanysurveys`.`companyid`))) on((`qrystatusnotes`.`companysurveyid` = `qrycompanysurveys`.`companysurveyid`))) on((`tntadven_wmsurvey`.`wmsurvey_users`.`username` = `tntadven_wmsurvey`.`wmsurvey_company_coach`.`username`))) |
i see the tables are innoDb, maybe the upgrade switched them to innodb, or maybe the upgrade changed the settings for the database and now the memory allocated is too low ?
You should tweak the database parameters, but i don't know if you can directly tweak it
Please advise if you see anything in the query's or if you are able to help figure out the issue. This is a client site and has been working fine until this upgrade. I am not sure what version of MySql or MariaDB was there before the upgrade. You can log in to the script here to see how long it takes to complete the page.
http://tntadventec.com/survey_system/main.php
U: vTechTeam
P: FrxVr