Оптимизация индексов в wordpress

Казалось бы, движок, который используется на сотнях тысяч веб-сайтов. Однако даже в его базе есть неправильно организованный индекс по innodb таблице. Исправляется очень легко и быстро. С чем это связано? с тем что в innodb таблицах значение primary ключа хранится вместе с любым вторичным индексом. Дополнительное использование в индексе самого primary key приводит к лишнему дублированию информации и увеличению объема занимаемого дискового пространства, соответственно к более медленным инсертам.

Для быстрого поиска таких индексов помогает полезная утилита  pt-duplicate-key-checker (простой скрипт на perl) из набора percona-toolkit. Данная тулза покажет сразу необходимый запрос для исправления ошибки.

В случае с worpress последней версии 3.6, запрос для исправления будет выглядеть следующим образом:
ALTER TABLE `wp_posts` DROP INDEX `type_status_date`, ADD INDEX `type_status_date` (`post_type`,`post_status`,`post_date`);

Заодно решил покрутить типы полей в базе, уж очень жирно для всех чисел использовать 8 байт. И уж очень неоптимально для 99% всех блогов на этом движке.
Ниже список sql запросов, которые приводят структуру большей части числовых полей к более оптимальной для небольших блогов.

ALTER TABLE `wp_commentmeta` CHANGE `meta_id` `meta_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
CHANGE `comment_id` `comment_id` SMALLINT UNSIGNED NOT NULL DEFAULT ‘0’;

ALTER TABLE `wp_comments` CHANGE `comment_ID` `comment_ID` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
CHANGE `comment_post_ID` `comment_post_ID` SMALLINT UNSIGNED NOT NULL DEFAULT ‘0’,
CHANGE `comment_parent` `comment_parent` SMALLINT UNSIGNED NOT NULL DEFAULT ‘0’,
CHANGE `user_id` `user_id` SMALLINT UNSIGNED NOT NULL DEFAULT ‘0’;

ALTER TABLE `wp_links` CHANGE `link_id` `link_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
CHANGE `link_owner` `link_owner` SMALLINT UNSIGNED NOT NULL DEFAULT ‘1’;

ALTER TABLE `wp_options` CHANGE `option_id` `option_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `wp_postmeta` CHANGE `meta_id` `meta_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
CHANGE `post_id` `post_id` SMALLINT UNSIGNED NOT NULL DEFAULT ‘0’;

ALTER TABLE `wp_posts` CHANGE `ID` `ID` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
CHANGE `post_author` `post_author` SMALLINT UNSIGNED NOT NULL DEFAULT ‘0’,
CHANGE `post_parent` `post_parent` SMALLINT UNSIGNED NOT NULL DEFAULT ‘0’,
CHANGE `comment_count` `comment_count` SMALLINT UNSIGNED NOT NULL DEFAULT ‘0’; /*поле по дефолту без unsigned*/

ALTER TABLE `wp_terms` CHANGE `term_id` `term_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
CHANGE `term_group` `term_group` SMALLINT UNSIGNED NOT NULL DEFAULT ‘0’; /*поле по дефолту без unsigned*/

ALTER TABLE `wp_term_relationships` CHANGE `object_id` `object_id` SMALLINT UNSIGNED NOT NULL DEFAULT ‘0’,
CHANGE `term_taxonomy_id` `term_taxonomy_id` SMALLINT UNSIGNED NOT NULL DEFAULT ‘0’,
CHANGE `term_order` `term_order` SMALLINT UNSIGNED NOT NULL DEFAULT ‘0’;

ALTER TABLE `wp_term_taxonomy` CHANGE `term_taxonomy_id` `term_taxonomy_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
CHANGE `term_id` `term_id` SMALLINT UNSIGNED NOT NULL DEFAULT ‘0’,
CHANGE `parent` `parent` SMALLINT UNSIGNED NOT NULL DEFAULT ‘0’,
CHANGE `count` `count` SMALLINT UNSIGNED NOT NULL DEFAULT ‘0’; /*поле по дефолту без unsigned*/

ALTER TABLE `wp_usermeta` CHANGE `umeta_id` `umeta_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
CHANGE `user_id` `user_id` SMALLINT UNSIGNED NOT NULL DEFAULT ‘0’;

ALTER TABLE `wp_users` CHANGE `ID` `ID` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT;

Оптимизация индексов в wordpress: 5 комментариев

  1. Отсутствие восторженных бото-комментов как лакмусовая бумажка для серьезной публикации :)

    Экспериментально выяснил, что PRIMARY INDEX, включенный в составной индекс type_status_date почему-то не работает в запросах вида

    SELECT ID
    FROM wp_posts
    WHERE ID IN (…)
    AND post_status = ‘…’
    AND post_type = ‘…’
    ORDER BY ID

    Участие принимают только type, status и date. Интересно, почему так происходит.

    1. чисто теоретически в InnoDB сам по себе primary ключ дублируется во всех индексах — это могло бы быть объяснением, если бы не filesort) откуда filesort — пока загадка.

  2. Опять я со своим первичным индексом :)

    Скорее на заметку. Имеется таблица ibf_posts вида

    pid, topic_id, post_date

    индексы висят на pic (PRIMARY) и на post_dateс (INDEX)

    Количество уникальных элементов одинаковое, но при этом сортировка по первичному ключу в связке с DISTINCT (без него ок) работает не так, как надо:

    EXPLAIN SELECT DISTINCT `topic_id`
    FROM `ibf_posts`
    ORDER BY `pid` DESC
    LIMIT 0 , 5
    //PRIMARY 4 NULL 249 Using temporary

    EXPLAIN SELECT DISTINCT `topic_id`
    FROM `ibf_posts`
    ORDER BY `post_date` DESC
    LIMIT 0 , 5
    //post_date 5 NULL 5 Using temporary

  3. Не подскажешь как разрулить эту проблему? http://wordpress.stackexchange.com/questions/205082/cannot-edit-or-add-new-page-after-migration-content-area-disappeared
    Человек вроде как ее победил, а я не могу. Какой запрос надо выполнить или может есть другая причина?… Не хватает знаний самостоятельно это сделать. Спасибо

    1. там в ответе в комментарии написано, что это изменение в версии 4.2 именно на этой странице. но можно добавить код, который там написан в php, чтобы вернуть редактирование. индексы тут вроде как ни при чем.

Добавить комментарий для idccc Отменить ответ

Ваш e-mail не будет опубликован.