[wp-trac] [WordPress Trac] #48514: Performance issue in COUNT query pluggable.php

[WordPress Trac]

#48514: Performance issue in COUNT query pluggable.php
-------------------------+-----------------------------
 Reporter:  sgoen        |      Owner:  (none)
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Comments     |    Version:  trunk
 Severity:  normal       |   Keywords:
  Focuses:  performance  |
-------------------------+-----------------------------
 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.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/48514>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform