Add index to comment_type and comment_approved fields in comment table

[spacedmonkey]

Follow on from #58368

It is common to query comments by comment_type or comment_approved. There are several query done in the admin to get counts for different screens. But for sites with many comments, this results in slow returning queries. Add database indexes to these two fields to improve database performance.

[spacedmonkey]

The gutenberg team is exploring inline comments. See ​ticket.

This ticket adds a new a custom comment type. This means that the new endpoints / code will query by comment type. As the comment table might get much large ( because of many inline comment ) these query will likely get much slower.

Adding an index here is simple, an a index for comment type, makes sense, as other table, like post index their type ( post type ).

Pinging members of the performance in hopes this change makes it into WP 6.8.

CC @flixos90 @swissspidy @joemcgill @mukesh27

[swissspidy]

Probably makes sense, but of course needs some numbers to prove that.

As the comment table might get much large ( because of many inline comment )

FWIW I doubt that. These comments are only temporary AFAIK.

[mauteri]

I am concerned about this too. GatherPress uses comment type for RSVPs. I'd also like to see an index on user_id in the comments table.

[adamsilverstein]

@spacedmonkey - Thanks for opening this issue. Do you have time to work on this before the 6.9 beta?

[Mamaduka]

Is this something we should consider for WP 7.0?

This change adds two single-column indexes to the comments table to improve the performance of queries that filter by comment type or by author user ID:

• KEY comment_type (comment_type)
• KEY user_id (user_id)

Both columns are queried directly in core (e.g. WP_Comment_Query filtering by type/user_id, and author comment counts) but were previously unindexed, forcing full table scans on large comments tables.

### Notes for reviewers

• comment_approved was intentionally left unindexed. The ticket mentions it, but comment_approved is already the leftmost column of the existing comment_approved_date_gmt (comment_approved, comment_date_gmt) index, so MySQL can satisfy comment_approved lookups via that index's leftmost prefix. A standalone index would be redundant.
• $wp_db_version bump is a placeholder. It is incremented minimally (61833 → 61834) so dbDelta() runs on existing installs; the final value should be set to the actual changeset revision at commit time.
• Indexes are added in wp-admin/includes/schema.php; existing installs pick them up automatically through dbDelta() during the database upgrade routine — no separate upgrade_NNN() function is required for index additions.

### Testing

• Verified the upgrade path: on an install at the prior db_version, running the DB upgrade adds both comment_type and user_id indexes to wp_comments.
• Verified fresh installs create the indexes via the updated schema.
• tests/phpunit/tests/db/dbDelta.php — 31/31 pass (confirms the schema string is well-formed).
• tests/phpunit/tests/comment/query.php — 156/156 pass (no query regressions).

Trac ticket: https://core.trac.wordpress.org/ticket/59488

## Use of AI Tools

AI assistance: Yes
Tool(s): Claude Code
Model(s): Claude Opus 4.8
Used for: Environment setup, drafting the schema change, running and interpreting the test suites, and writing this description. The index design decision (skipping the redundant comment_approved index) and all changes were reviewed and approved by me.

[gonzomir]

@mauteri I tested the above PR on a project I'm working on with 3M comments and it didn't help much. What did help, although queries for counting comments per status remained slow, was index on both comment_approved and comment_type. The speed of the queries improved 5 times with index on both fields, while the index only on comment_type didn't have any effect and didn't get picked by MySQL (MariaDB 10.11.14 to be precise).

[mauteri]

@gonzomir Thanks for testing at that scale. I've updated the PR to replace the standalone comment_type index with a composite comment_type_approved (comment_type, comment_approved). Went with comment_type first so the same index also covers type-only lookups via the leftmost prefix. status-only queries are already handled by the existing comment_approved_date_gmt index. user_id is unchanged. If you get a chance, would be great to hear if the composite performs the same as the one you tested on your 3M-comment site.

[gonzomir]

@mauteri I tested the patch and the composite index, the patch applies OK and the index performs much better. Depending on the exact query they run 3 to 5 times faster - from 3-4-5 seconds to around 1 second.

Before:

MariaDB [db]> explain SELECT COUNT(*) FROM wp_comments WHERE ( ( comment_approved = '0' OR comment_approved = '1' ) ) AND comment_type IN ('', 'comment') AND comment_type NOT IN ('note');
+------+-------------+-------------+------+---------------------------+------+---------+------+---------+-------------+
| id   | select_type | table       | type | possible_keys             | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------------+------+---------------------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | wp_comments | ALL  | comment_approved_date_gmt | NULL | NULL    | NULL | 3545832 | Using where |
+------+-------------+-------------+------+---------------------------+------+---------+------+---------+-------------+
1 row in set (0.003 sec)

MariaDB [db]> SELECT COUNT(*) FROM wp_comments WHERE ( ( comment_approved = '0' OR comment_approved = '1' ) ) AND comment_type IN ('', 'comment') AND comment_type NOT IN ('note');
+----------+
| COUNT(*) |
+----------+
|  3285326 |
+----------+
1 row in set (5.655 sec)

After:

MariaDB [db]> explain SELECT COUNT(*) FROM wp_comments WHERE ( ( comment_approved = '0' OR comment_approved = '1' ) ) AND comment_type IN ('', 'comment') AND comment_type NOT IN ('note');
+------+-------------+-------------+-------+-------------------------------------------------+-----------------------+---------+------+---------+--------------------------+
| id   | select_type | table       | type  | possible_keys                                   | key                   | key_len | ref  | rows    | Extra                    |
+------+-------------+-------------+-------+-------------------------------------------------+-----------------------+---------+------+---------+--------------------------+
|    1 | SIMPLE      | wp_comments | range | comment_approved_date_gmt,comment_type_approved | comment_type_approved | 164     | NULL | 2705896 | Using where; Using index |
+------+-------------+-------------+-------+-------------------------------------------------+-----------------------+---------+------+---------+--------------------------+
1 row in set (0.001 sec)

MariaDB [db]> SELECT COUNT(*) FROM wp_comments WHERE ( ( comment_approved = '0' OR comment_approved = '1' ) ) AND comment_type IN ('', 'comment') AND comment_type NOT IN ('note');
+----------+
| COUNT(*) |
+----------+
|  3285326 |
+----------+
1 row in set (1.096 sec)