Notifications
Clear all
Limited Support
Our support team is currently on holiday from December 25, 2025 to January 7, 2026, and replies may be delayed during this period.
We appreciate your patience and understanding while our team is away. Thank you for being part of the gVectors community!
Merry Christmas and Happy Holidays! 🎄
Topic starter
06/01/2019 2:47 am
Hello
My server host has recently informed me of the following issue:
mysql> SELECT COUNT() FROM wp_comments WHERE ( comment_approved = '1' ) AND comment_type != 'private' ORDER BY wp_comments.comment_date_gmt DESC ;
+----------+
| COUNT( ) |
+----------+
| 486669 |
+----------+
1 row in set (3.63 sec)
Effectively the comment system is continuously trying to calculate the total COUNT of all non-private comments, which takes several seconds per query.
Can you please let me know which of the WpDiscuz features would be causing this?
06/01/2019 12:35 pm
This SQl query need to generating comment threads for website visitors. The problem is in your table structure, not in the query. As far as I see your wp_comments table don't have appropriate indexes so it takes so long time. Please open phpMyAdmin Database manager of your hosting service > cPanel, navigate to WordPress database, press on SQL Tab and execute this query. It'll create some important table indexes to make this query very fast:
ALTER TABLE `wp_comments` ADD INDEX( `comment_approved`, `comment_type`);
ALTER TABLE `wp_comments` ADD INDEX( `comment_post_ID`, `comment_approved`);
ALTER TABLE `wp_comments` ADD INDEX( `comment_post_ID`, `comment_approved`, `comment_type`);
It may take a while, so you can execute them one by one with three times.
Topic starter
08/01/2019 4:37 am
Thank you i have asked my server hosts to enter this code.
Topic starter
08/01/2019 5:43 am
Hello
Please see the note from my server hosts. How can I respond?
We've tried to implement various indexes including the ones supplied by the developer, but unfortunately the indexes can not be added:
mysql> ALTER TABLE wp_comments ADD INDEX( comment_approved, comment_type);
ERROR 1067 (42000): Invalid default value for 'comment_date'
mysql> ALTER TABLE wp_comments ADD INDEX( comment_post_ID, comment_approved);
ERROR 1067 (42000): Invalid default value for 'comment_date'
mysql> ALTER TABLE wp_comments ADD INDEX( comment_post_ID, comment_approved, comment_type);
ERROR 1067 (42000): Invalid default value for 'comment_date'This is because the "comment_date" field is type "datetime", which according to the MySQL docs is limited to the range '1000-01-01 00:00:00' to '9999-12-31 23:59:59' (refer to https://dev.mysql.com/doc/refman/5.7/en/datetime.html ). The default value for the comment_date field is "0000-00-00 00:00:00" which is outside this range.
We could try altering the table to fix this default value; please check with your developer if this would be OK.
08/01/2019 12:24 pm
Hi @weninrome,
You should add this line before those SQLs.
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
They should be executed together, like this:
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
ALTER TABLE `wp_comments` ADD INDEX( `comment_approved`, `comment_type`);
ALTER TABLE `wp_comments` ADD INDEX( `comment_post_ID`, `comment_approved`);
ALTER TABLE `wp_comments` ADD INDEX( `comment_post_ID`, `comment_approved`, `comment_type`);