Problem: WP is slow or dead when editing pages.
Possible Cause: MySQL slow query related to wp_postmeta or wp_options
SELECT DISTINCT meta_key
FROM wp_postmeta
WHERE meta_key NOT BETWEEN '_'
AND '_z'
HAVING meta_key NOT LIKE '\\_%'
ORDER BY meta_key
LIMIT 30"
The above query took over 60+ seconds to complete causing the PAGE COULD NOT BE LOADED. This query only runs when EDITING a Page or Post.
Possible Solution:
I did some searching on this query and the slowness is on large wp_postmeta tables
and the WP updated to 4.2 changing the MySQL Encoding and Collation from UTF8 to UTF8MB4.
The Index Key meta_key doesn't work correctly with UTF8MB4 when it's VARCHAR(255).
The index is bypassed for whatever reason with the above Query.
I changed meta_key from VARCHAR(255) to VARCHAR(191).
That above query now is lighting fast. Changed from 60+ Seconds to under a second.
It fixed it.
See: https://core.trac.wordpress.org/ticket/33885#comment:2
Debugging:
mysql> SHOW FULL COLUMNS FROM wp_postmeta;
+------------+---------------------+------------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+------------+---------------------+------------------------+------+-----+---------+----------------+---------------------------------+---------+
| meta_id | bigint(20) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| post_id | bigint(20) unsigned | NULL | NO | MUL | 0 | | select,insert,update,references | |
| meta_key | varchar(255) | utf8mb4_unicode_520_ci | YES | MUL | NULL | | select,insert,update,references | |
| meta_value | longtext | utf8mb4_unicode_520_ci | YES | | NULL | | select,insert,update,references | |
+------------+---------------------+------------------------+------+-----+---------+----------------+---------------------------------+---------+
mysql> SHOW TABLE STATUS where name like 'wp_postmeta';
+-------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+------------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+------------------------+----------+----------------+---------+
| wp_postmeta | InnoDB | 10 | Compact | 184091 | 1142 | 210321408 | 0 | 16842752 | 4194304 | 245758 | 2018-05-01 04:10:14 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
+-------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+------------------------+----------+----------------+---------+
1 row in set (0.22 sec)