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