Opened 5 years ago
Closed 5 years ago
#48514 closed enhancement (fixed)
Performance issue in COUNT query pluggable.php
Reported by: |
|
Owned by: |
|
---|---|---|---|
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)
Change History (4)
#1
@
5 years ago
- Milestone changed from Awaiting Review to 5.4
- Owner set to SergeyBiryukov
- Status changed from new to reviewing
#2
@
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.
Note: See
TracTickets for help on using
tickets.
patch for pluggable.php