Changes between Initial Version and Version 1 of Ticket #33885, comment 86
- Timestamp:
- 08/26/2021 11:49:29 AM (3 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
Ticket #33885, comment 86
initial v1 27 27 Good covering indexes often are *compound indexes* indexing multiple columns. And they often serve to optimize multiple query patterns, not just one. 28 28 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.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 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. 30 30 31 31 If 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 33 We hope our experience can inform the core developers' decisions about any future changes to the database schema.