it seems you're in holiday. I confer with of my friends who is talented in SQL and show him the slowlog query. here is his recommendations:
we shouldn't use datediff, it has lots of pressure, I need to use static date, take look at this query:
SELECT DATEDIFF('2014-07-06 09:13:26', a.`created` ) as `noofdays`, DATEDIFF('2014-07-06 09:13:26', IF(a.`replied` = '0000-00-00 00:00:00', a.`created`, a.`replied`) ) as `daydiff`, TIMEDIFF('2014-07-06 09:13:26', IF(a.`replied` = '0000-00-00 00:00:00', a.`created`, a.`replied`) ) as `timediff`, (SELECT COUNT(1) FROM `unurc_discuss_polls` WHERE `post_id` = a.`id`) AS `polls_cnt`, (SELECT COUNT(1) FROM `discuss_favourites` WHERE `post_id` = a.`id`) AS `totalFavourites`,(SELECT COUNT(1) FROM `unurc_discuss_posts` WHERE `parent_id` = a.`id` AND `published`="1") AS `num_replies`, (SELECT COUNT(1) FROM `discuss_attachments` WHERE `uid` = a.`id` AND `type`='questions' AND `published`='1') AS `attachments_cnt`, (SELECT COUNT(1) FROM `discuss_votes` WHERE `post_id` = a.`id` AND `user_id` = '7351') AS `isVoted`, a.`post_status`, a.`post_type`, pt.`suffix` AS post_type_suffix, pt.`title` AS post_type_title , a.*, e.`title` AS `category`, a.`legacy`, IF(a.`replied` = '0000-00-00 00:00:00', a.`created`, a.`replied`) as `lastupdate`, (select count(1) from `discuss_votes` where post_id = a.id) as `total_vote_cnt` FROM `discuss_posts` AS a LEFT JOIN `discuss_post_types` AS pt ON a.`post_type`= pt.`alias` LEFT JOIN `discuss_category` AS e ON a.`category_id`=e.`id` WHERE a.`published` = '1' AND a.`featured` = '0' AND a.`parent_id` = '0' AND a.`answered` = '0' AND a.`category_id` IN ('8','10','13','24','25','26','27','28','29','30','32','3','17','18','19','20','21','22','23','2','31','33','34','35','4','44','45','46','47','48','49','50','51','52','53','5','36','37','38','39','40','41','42','43','54','55','6','56','57','58','59','60','61','62','63','64','65','66','92','7','67','68','69','9','70','71','72','94','11','73','74','75','76','77','12','78','79','80','81','82','83','84','14','85','86','87','88','89','90','91','15','93') AND a.`private`='0' ORDER BY a.`replied` DESC LIMIT 0, 50;
# Query_time: 5.047457 Lock_time: 0.000163 Rows_sent: 48 Rows_examined: 13715
We are not suppose to use IF in SELECT Statement, you should handle it in your code. sql is not good for this because for each individual row it apply the IF.
for date diff 13715 row effected. it's terrible. IF will disable Paging either.
Look at this:
SELECT COUNT(1) AS CNT, `user_id` FROM `discuss_posts` WHERE `user_id` IN (7075,564,6224,4273,7805,8483,543,8544,6769,6960,6235,7341,7692,476,5988,8516,8555,8130,1182,1115,403,8230,7164,7700,8328,1630,7597,7809,6227,8562,8511,1668,8547,7617,8087,8549,7760,8131,8532,8559,6928,1184,7544,8556,1450,7123,980,7391) AND `parent_id` = 0 AND `published` = 1 group by `user_id`;
# User@Host: j---- @ localhost [] Id: 18738
#
Query_time: 16.452306 Lock_time: 6.582165 Rows_sent: 50 Rows_examined: 25996
Using IN is not recommended like this. you should use Temp Table and use Join instead of Where Statement IN. as you can see the IN statement kills sql.
Another bad Query:
SELECT DISTINCT( a.`id` ), a.`points` AS `total_points`, COUNT( b.`id` ) AS `total_answers`
FROM `unurc_discuss_users` AS a LEFT JOIN `unurc_discuss_posts` AS b ON a.`id` = b.`user_id`
AND b.`answered` = '1' AND b.`parent_id` != '0' INNER JOIN `unurc_users` AS c ON c.`id` = a.`id`
GROUP BY a.`id` ORDER BY total_points DESC LIMIT 0,5;
Query_time: 9.4341306
you should try to not to use LEFT JOIN. he recommend you to make table which contains Posts and UserID to omit the Left Join. in addition you are not suppose to use DISTINC and Group by.
I can see sometimes table will lock for more than 12sec, in MSSQL we can use (with nolock) parameter. we have something equal in MYSQL:
http://itecsoftware.com/with-nolock-table-hint-equivalent-for-mysql
full log in enclosed.
Thank you for cooperation.