OK, this is a strange one. I have list which has been growing and growing and now it times out when trying to view it, this is the query:
I have spend some time debugging this and found that the problem is SQL_CALC_FOUND_ROWS. If I remove that and run it in phpMyAdmin, the query takes about 0.15 seconds to execute. If it leave it in, the query is currently taking about 45 mins to complete.
Admittedly it is not the most optimised query in the world but it looks like something is going seriously wrong with such a big difference. The following are the rough numbers of records in each table:
fabrik_matches - 400,000
fabrik_requests - 5,000
jos_users - 100
So the amount of data being processed is big enough, but not outrageous. However the joins seems to kill it when it has to count the them all before returning the first 100.
After some research I found this:
http://bugs.mysql.com/bug.php?id=18454
So I upgraded from MySQL 5.5 to 5.6 but it didn't help.
I have spent hours on this and I'm at a loss what to try next. I have tried optimising the query manually but nothing even makes a dent in the processing time except removing SQL_CALC_FOUND_ROWS.
To be honest, this list is a tool which users would use the filters to drill down to just 1 request so counting the total including the joins is not useful in this case, pagination is not even necessary.
So, back to the original question, is there any way to disable SQL_CALC_FOUND_ROWS? Or is there another solution anyone can think of? This is crippling as the matches table continues to grow but the managers currently have no way to view the data.
Many thanks for any help you can give.
Code:
SELECT SQL_CALC_FOUND_ROWS DISTINCT `fabrik_matches`.`id` AS `fabrik_matches___id` , `fabrik_matches`.`id` AS `fabrik_matches___id_raw` , `fabrik_matches`.`group` AS `fabrik_matches___group` , `fabrik_matches`.`group` AS `fabrik_matches___group_raw` , `fabrik_matches`.`mode` AS `fabrik_matches___mode_raw` , `fabrik_requests_1`.`camera` AS `fabrik_matches___mode` , `fabrik_matches`.`type` AS `fabrik_matches___type` , `fabrik_matches`.`type` AS `fabrik_matches___type_raw` , `fabrik_matches`.`request1` AS `fabrik_matches___request1_raw` , `fabrik_requests`.`id` AS `fabrik_matches___request1` , `fabrik_matches`.`user1` AS `fabrik_matches___user1_raw` , `jos_users`.`name` AS `fabrik_matches___user1` , `fabrik_matches`.`user2` AS `fabrik_matches___user2_raw` , `jos_users_0`.`name` AS `fabrik_matches___user2` , `fabrik_matches`.`date_time` AS `fabrik_matches___date_time` , `fabrik_matches`.`date_time` AS `fabrik_matches___date_time_raw` , `fabrik_matches`.`match` AS `fabrik_matches___match` , `fabrik_matches`.`match` AS `fabrik_matches___match_raw` , `fabrik_matches`.`frame` AS `fabrik_matches___frame` , `fabrik_matches`.`frame` AS `fabrik_matches___frame_raw` , `fabrik_matches`.`score0` AS `fabrik_matches___score0` , `fabrik_matches`.`score0` AS `fabrik_matches___score0_raw` , `fabrik_matches`.`score1` AS `fabrik_matches___score1` , `fabrik_matches`.`score1` AS `fabrik_matches___score1_raw` , `fabrik_matches`.`score2` AS `fabrik_matches___score2` , `fabrik_matches`.`score2` AS `fabrik_matches___score2_raw` , `fabrik_matches`.`score3` AS `fabrik_matches___score3` , `fabrik_matches`.`score3` AS `fabrik_matches___score3_raw` , `fabrik_matches`.`score4` AS `fabrik_matches___score4` , `fabrik_matches`.`score4` AS `fabrik_matches___score4_raw` , `fabrik_matches`.`score5` AS `fabrik_matches___score5` , `fabrik_matches`.`score5` AS `fabrik_matches___score5_raw` , `fabrik_matches`.`score6` AS `fabrik_matches___score6` , `fabrik_matches`.`score6` AS `fabrik_matches___score6_raw` , `fabrik_matches`.`score7` AS `fabrik_matches___score7` , `fabrik_matches`.`score7` AS `fabrik_matches___score7_raw` , `fabrik_matches`.`score8` AS `fabrik_matches___score8` , `fabrik_matches`.`score8` AS `fabrik_matches___score8_raw` , `fabrik_matches`.`score9` AS `fabrik_matches___score9` , `fabrik_matches`.`score9` AS `fabrik_matches___score9_raw` , `fabrik_matches`.`id` AS slug, `fabrik_matches`.`id` AS `__pk_val`
FROM `fabrik_matches`
LEFT JOIN `jos_users` AS `jos_users` ON `jos_users`.`id` = `fabrik_matches`.`user1`
LEFT JOIN `jos_users` AS `jos_users_0` ON `jos_users_0`.`id` = `fabrik_matches`.`user2`
LEFT JOIN `fabrik_requests` AS `fabrik_requests` ON `fabrik_requests`.`token` = `fabrik_matches`.`request1`
LEFT JOIN `fabrik_requests` AS `fabrik_requests_0` ON `fabrik_requests_0`.`token` = `fabrik_matches`.`request2`
LEFT JOIN `fabrik_requests` AS `fabrik_requests_1` ON `fabrik_requests_1`.`camera` = `fabrik_matches`.`mode`
LIMIT 0 , 100
Admittedly it is not the most optimised query in the world but it looks like something is going seriously wrong with such a big difference. The following are the rough numbers of records in each table:
fabrik_matches - 400,000
fabrik_requests - 5,000
jos_users - 100
So the amount of data being processed is big enough, but not outrageous. However the joins seems to kill it when it has to count the them all before returning the first 100.
After some research I found this:
http://bugs.mysql.com/bug.php?id=18454
So I upgraded from MySQL 5.5 to 5.6 but it didn't help.
I have spent hours on this and I'm at a loss what to try next. I have tried optimising the query manually but nothing even makes a dent in the processing time except removing SQL_CALC_FOUND_ROWS.
To be honest, this list is a tool which users would use the filters to drill down to just 1 request so counting the total including the joins is not useful in this case, pagination is not even necessary.
So, back to the original question, is there any way to disable SQL_CALC_FOUND_ROWS? Or is there another solution anyone can think of? This is crippling as the matches table continues to grow but the managers currently have no way to view the data.
Many thanks for any help you can give.