Performance issue in COUNT query pluggable.php

[sgoen]

I found a query in pluggable.php (checked both trunk, b.3-branch and 5.2-branch) which is very inefficient as it uses count(<column_name>). Using count(*) or count(1) instead of count(<COL>) greatly increases performance as shown in the examples below.

MariaDB [wordpress]> explain SELECT count(comment_ID) FROM wp_comments WHERE comment_approved = '0';
+------+-------------+-------------+------+---------------------------+---------------------------+---------+-------+--------+-----------------------+
| id   | select_type | table       | type | possible_keys             | key                       | key_len | ref   | rows   | Extra                 |
+------+-------------+-------------+------+---------------------------+---------------------------+---------+-------+--------+-----------------------+
|    1 | SIMPLE      | wp_comments | ref  | comment_approved_date_gmt | comment_approved_date_gmt | 82      | const | 731259 | Using index condition |
+------+-------------+-------------+------+---------------------------+---------------------------+---------+-------+--------+-----------------------+
1 row in set (0.00 sec)

MariaDB [wordpress]> explain SELECT count(*) FROM wp_comments WHERE comment_approved = '0';
+------+-------------+-------------+------+---------------------------+---------------------------+---------+-------+--------+--------------------------+
| id   | select_type | table       | type | possible_keys             | key                       | key_len | ref   | rows   | Extra                    |
+------+-------------+-------------+------+---------------------------+---------------------------+---------+-------+--------+--------------------------+
|    1 | SIMPLE      | wp_comments | ref  | comment_approved_date_gmt | comment_approved_date_gmt | 82      | const | 731259 | Using where; Using index |
+------+-------------+-------------+------+---------------------------+---------------------------+---------+-------+--------+--------------------------+
1 row in set (0.00 sec)

MariaDB [wordpress]> SELECT count(comment_ID) FROM wp_comments WHERE comment_approved = '0';
+-------------------+
| count(comment_ID) |
+-------------------+
|            730800 |
+-------------------+
1 row in set (16.00 sec)

MariaDB [wordpress]> SELECT count(*) FROM wp_comments WHERE comment_approved = '0';
+----------+
| count(*) |
+----------+
|   730800 |
+----------+
1 row in set (0.42 sec)

Note that these results where NOT cached. I added a diff which is based on the master branch.

[sgoen]

patch for pluggable.php

[SergeyBiryukov]

Hi there, welcome to WordPress Trac! Thanks for the ticket.

I was able to reproduce these results with 730000+ comments created via wp comment generate with WP-CLI.

In my case, the results were 6.3706 seconds for count(comment_ID) vs. 0.4937 seconds for count(*), which is still quite a noticeable improvement.

[SergeyBiryukov]

In 46878:

Comments: Optimize the query for pending comments count in wp_notify_moderator() for better performance on large tables.

Props sgoen.
Fixes #48514.