Add wp_posts index: type_status_author #9312
Conversation
See https://core.trac.wordpress.org/ticket/50161 When you have a large number of posts, not having this index can significantly slow down some DB queries.
|
Hi @josephscott! 👋 Thank you for your contribution to WordPress! 💖 It looks like this is your first pull request to No one monitors this repository for new pull requests. Pull requests must be attached to a Trac ticket to be considered for inclusion in WordPress Core. To attach a pull request to a Trac ticket, please include the ticket's full URL in your pull request description. Pull requests are never merged on GitHub. The WordPress codebase continues to be managed through the SVN repository that this GitHub repository mirrors. Please feel free to open pull requests to work on any contribution you are making. More information about how GitHub pull requests can be used to contribute to WordPress can be found in the Core Handbook. Please include automated tests. Including tests in your pull request is one way to help your patch be considered faster. To learn about WordPress' test suites, visit the Automated Testing page in the handbook. If you have not had a chance, please review the Contribute with Code page in the WordPress Core Handbook. The Developer Hub also documents the various coding standards that are followed:
Thank you, |
|
The following accounts have interacted with this PR and/or linked issues. I will continue to update these lists as activity occurs. You can also manually ask me to refresh this list by adding the Core Committers: Use this line as a base for the props when committing in SVN: To understand the WordPress project's expectations around crediting contributors, please review the Contributor Attribution page in the Core Handbook. |
Test using WordPress PlaygroundThe changes in this pull request can previewed and tested using a WordPress Playground instance. WordPress Playground is an experimental project that creates a full WordPress instance entirely within the browser. Some things to be aware of
For more details about these limitations and more, check out the Limitations page in the WordPress Playground documentation. |
|
@josephscott In https://core.trac.wordpress.org/ticket/15499 we explored adding an index for the |
|
Unfortunately that ticket, which was opened 15 years ago, ended up like so many other database performance improvement discussions - demonstration of being able to make significant improvements in query times, but no one actually gets it committed. |
sirreal
left a comment
sirreal
left a comment
There was a problem hiding this comment.
I tested and this seems to work well. I provided more extensive feedback and reasoning in trac.
| if ( $wp_current_db_version < 60498 ) { | ||
| $wpdb->query( "ALTER TABLE $wpdb->posts ADD INDEX type_status_author (post_type,post_status,post_author)" ); | ||
| } |
There was a problem hiding this comment.
I checked the index before and after this change, it's working correctly 👍
Before
MariaDB [wordpress]> SHOW INDEX FROM wp_posts;
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| wp_posts | 0 | PRIMARY | 1 | ID | A | 1094 | NULL | NULL | | BTREE | | | NO |
| wp_posts | 1 | post_name | 1 | post_name | A | 273 | 191 | NULL | | BTREE | | | NO |
| wp_posts | 1 | type_status_date | 1 | post_type | A | 10 | NULL | NULL | | BTREE | | | NO |
| wp_posts | 1 | type_status_date | 2 | post_status | A | 15 | NULL | NULL | | BTREE | | | NO |
| wp_posts | 1 | type_status_date | 3 | post_date | A | 1094 | NULL | NULL | | BTREE | | | NO |
| wp_posts | 1 | type_status_date | 4 | ID | A | 1094 | NULL | NULL | | BTREE | | | NO |
| wp_posts | 1 | post_parent | 1 | post_parent | A | 136 | NULL | NULL | | BTREE | | | NO |
| wp_posts | 1 | post_author | 1 | post_author | A | 2 | NULL | NULL | | BTREE | | | NO |
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
8 rows in set (0.001 sec)
After
MariaDB [wordpress]> SHOW INDEX FROM wp_posts;
+----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| wp_posts | 0 | PRIMARY | 1 | ID | A | 972 | NULL | NULL | | BTREE | | | NO |
| wp_posts | 1 | post_name | 1 | post_name | A | 324 | 191 | NULL | | BTREE | | | NO |
| wp_posts | 1 | type_status_date | 1 | post_type | A | 10 | NULL | NULL | | BTREE | | | NO |
| wp_posts | 1 | type_status_date | 2 | post_status | A | 15 | NULL | NULL | | BTREE | | | NO |
| wp_posts | 1 | type_status_date | 3 | post_date | A | 972 | NULL | NULL | | BTREE | | | NO |
| wp_posts | 1 | type_status_date | 4 | ID | A | 972 | NULL | NULL | | BTREE | | | NO |
| wp_posts | 1 | post_parent | 1 | post_parent | A | 138 | NULL | NULL | | BTREE | | | NO |
| wp_posts | 1 | post_author | 1 | post_author | A | 2 | NULL | NULL | | BTREE | | | NO |
| wp_posts | 1 | type_status_author | 1 | post_type | A | 10 | NULL | NULL | | BTREE | | | NO |
| wp_posts | 1 | type_status_author | 2 | post_status | A | 15 | NULL | NULL | | BTREE | | | NO |
| wp_posts | 1 | type_status_author | 3 | post_author | A | 19 | NULL | NULL | | BTREE | | | NO |
+----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
11 rows in set (0.001 sec)
|
@josephscott Thanks for updating. @SergeyBiryukov Let's commit this one ASAP so it get well tested before the final release of 6.9 |
3 participants
See https://core.trac.wordpress.org/ticket/50161
When you have a large number of posts, not having this index can significantly slow down some DB queries.