WordPress.org

Make WordPress Core

Changes between Initial Version and Version 1 of Ticket #33885, comment 86


Ignore:
Timestamp:
08/26/2021 11:49:29 AM (8 weeks ago)
Author:
OllieJones
Comment:

Legend:

Unmodified
Added
Removed
Modified
  • Ticket #33885, comment 86

    initial v1  
    2727Good covering indexes often are *compound indexes* indexing multiple columns. And they often serve to optimize multiple query patterns, not just one.
    2828
    29 Recent MySQL versions (specifically those with the InnoDB Barracuda engine) don't have the 767-byte limit on indexes: it's 3072 [https://dev.mysql.com/doc/refman/5.7/en/innodb-limits.html]. Barracuda has been available since MySQL 5.7, with some versions of 5.6 also supporting it. MariaDB 10.2 and beyond supports Barracuda too.  So the prefix indexes aren't necessary any more.
     29Recent MySQL versions (specifically those with the InnoDB Barracuda engine) don't have the 767-byte limit on indexes: it's 3072 [https://dev.mysql.com/doc/refman/5.7/en/innodb-limits.html]. Barracuda has been available since MySQL 5.7, with some versions of 5.6 also supporting it. MariaDB 10.2 and beyond supports Barracuda too.  So the prefix indexes on the `VHARCHAR(250)` columns aren't necessary any more. If you want indexes on the `meta_value LONGTEXT` columns you still absolutely need prefix indexes. But core doesn't have any indexes like that.
    3030
    3131If any change is necessary in core, it's to the indexes not the tables.  Rick James and I have been working on a plugin to reindex the `*meta` tables and a few others. [https://wordpress.org/plugins/index-wp-mysql-for-speed/]. It's still pretty new, but it seems to help. It helps users upgrade from MyISAM to InnoDB, then adds some compound indexes. You can read about the theory of operation. here. [https://www.plumislandmedia.net/wordpress/speeding-up-wordpress-database-operations/]
     32
     33We hope our experience can inform the core developers' decisions about any future changes to the database schema.