We've just moved our db server onto a new machine and upgraded to mysql 5.6 from 5.0. we have a bailout server that is kept in sync and is still at mysql 5.0, all tables are MyISAM, and I'm hitting slowdown on the new server. I've run Check, Analyze, Repair, Optimize on all relevant tables, on both servers, and still the new server takes approx 20 seconds against the old server's 0.2 seconds to run the following query.
SELECT DISTINCT `personBaseData`.`PersonID`,
`personBaseData`.`Forename`,
`personBaseData`.`Nickname`,
`personBaseData`.`Surname`,
COUNT(*) AS Count
FROM `personBaseData`
LEFT JOIN `sessionAttendance`
ON (( `personBaseData`.`PersonID` =
`sessionAttendance`.`ContactID` )
AND ( `sessionAttendance`.`ContactType` = 'Individual' ) )
JOIN `sessionBaseData`
ON (( `sessionAttendance`.`SessionID` = `sessionBaseData`.`SessionID` ))
JOIN `sessionGroupBaseData`
ON (( `sessionBaseData`.`SessionGroupID` = `sessionGroupBaseData`.`SessionGroupID` ))
WHERE `personBaseData`.`PersonID` IN (SELECT `PersonID`
FROM `personFlexData`
WHERE ( `flexName` = 'Organisation_P_27'
AND `flexValue` = 'HCCT' ))
AND `sessionBaseData`.`StartDate` > '2013-08-15'
AND `sessionAttendance`.`Attended` = '1'
AND `sessionGroupBaseData`.`Title` = 'Open Access (HCCT)'
AND `personBaseData`.`Type` & '1'
GROUP BY `personBaseData`.`PersonID`
ORDER BY Count DESC,
`personBaseData`.`Forename` ASC,
`personBaseData`.`Nickname` ASC,
`personBaseData`.`Surname` ASC ;
Running explain on this sql on both servers, come back with
New 5.6 Server
Bailout 5.0 Server
Is there anything obvious I can pursue, looks like a walk through the variables, and looking for anything obvious, but I'm not exactly sure what the main kickers might be, but clearly I'd like to be doing Primary selects not Simple ones, and only having one large row count.
Thanks in advance
