WordPress.org

Make WordPress Core

Opened 12 days ago

Last modified 12 days ago

#48514 reviewing enhancement

Performance issue in COUNT query pluggable.php

Reported by: sgoen Owned by: SergeyBiryukov
Milestone: 5.4 Priority: normal
Severity: normal Version: 5.3
Component: Comments Keywords:
Focuses: performance Cc:
PR Number:

Description

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.

Attachments (1)

pluggable.php.diff (719 bytes) - added by sgoen 12 days ago.
patch for pluggable.php

Download all attachments as: .zip

Change History (2)

@sgoen
12 days ago

patch for pluggable.php

#1 @SergeyBiryukov
12 days ago

  • Milestone changed from Awaiting Review to 5.4
  • Owner set to SergeyBiryukov
  • Status changed from new to reviewing
Note: See TracTickets for help on using tickets.