Make WordPress Core

Opened 5 years ago

Closed 5 years ago

#48514 closed enhancement (fixed)

Performance issue in COUNT query pluggable.php

Reported by: sgoen's profile sgoen Owned by: sergeybiryukov's profile SergeyBiryukov
Milestone: 5.4 Priority: normal
Severity: normal Version: 5.3
Component: Comments Keywords:
Focuses: performance Cc:

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 5 years ago.
patch for pluggable.php

Download all attachments as: .zip

Change History (4)

@sgoen
5 years ago

patch for pluggable.php

#1 @SergeyBiryukov
5 years ago

  • Milestone changed from Awaiting Review to 5.4
  • Owner set to SergeyBiryukov
  • Status changed from new to reviewing

#2 @SergeyBiryukov
5 years ago

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.

#3 @SergeyBiryukov
5 years ago

  • Resolution set to fixed
  • Status changed from reviewing to closed

In 46878:

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

Props sgoen.
Fixes #48514.

Note: See TracTickets for help on using tickets.