Улучшение запроса с использованием большого количества внутренних соединений для wp_postmeta, таблицы ключей / значений

я работаю с веб-сайтом WordPress, который выполняет следующий запрос, но я вижу, что этот запрос выполняет множество внутренних объединений, и веб-сайт занимает много времени, чтобы загружать и опускаться много, и я пытался создать запрос, который дает тот же результат но еще не успел

Я хотел бы знать, что может быть лучшим способом сделать это

SELECT * FROM wp_posts INNER JOIN wp_postmeta color ON wp_posts.ID = color.post_id INNER JOIN wp_postmeta transmission ON wp_posts.ID = transmission.post_id INNER JOIN wp_postmeta model ON wp_posts.ID = model.post_id INNER JOIN wp_postmeta brand ON wp_posts.ID = brand.post_id AND color.meta_key = 'color' AND color.meta_value = 'red' AND transmission.meta_key = 'transmission' AND transmission.meta_value = 'auto' AND model.meta_key = 'model' AND model.meta_value = 'model' AND brand.meta_key = 'brand' AND brand.meta_value = 'brand' AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'car' ORDER BY wp_posts.post_title 

Вот объяснение.

 +----+-------------+-----------+--------+-----------------------------+----------+---------+------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+--------+-----------------------------+----------+---------+------------------------+------+----------------------------------------------+ | 1 | SIMPLE | color | ref | post_id,meta_key | meta_key | 768 | const | 629 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | wp_posts | eq_ref | PRIMARY,type_status_date,ID | PRIMARY | 8 | tmcdb.color.post_id | 1 | Using where | | 1 | SIMPLE | brand | ref | post_id,meta_key | post_id | 8 | tmcdb.wp_posts.ID | 4 | Using where | | 1 | SIMPLE | transmission | ref | post_id,meta_key | post_id | 8 | tmcdb.color.post_id | 4 | Using where | | 1 | SIMPLE | model | ref | post_id,meta_key | post_id | 8 | tmcdb.transmission.post_id | 4 | Using where | +----+-------------+-----------+--------+-----------------------------+----------+---------+------------------------+------+----------------------------------------------+ 

Схема WordPress здесь.

Кажется, вы пытаетесь получить набор результатов с одной строкой за сообщение типа car . Кажется, вы хотите отображать различные атрибуты каждого автомобиля в сообщении, и они откладываются в postmeta .

Pro tip: Никогда не используйте SELECT * в программном обеспечении, если вы не знаете, почему вы это делаете. В частности, с запросами, содержащими множество операций JOIN , SELECT * возвращает много бессмысленных и избыточных столбцов.

Для таблицы WordPress postmeta трюк с дизайном запроса. Если вы хотите получить определенный атрибут, сделайте следующее:

  SELECT p.ID, p.post_title, color.meta_value AS color FROM wp_posts AS p LEFT JOIN wp_postmeta AS color ON p.ID = color.post_id AND 'color' = color.meta_key WHERE p.post_status = 'publish' AND /* etc etc */ 

Крайне важно понять этот шаблон, когда делаете то, что вы пытаетесь сделать. Этот шаблон необходим, поскольку postmeta является своеобразным типом таблицы, называемой хранилищем ключей / значений. Что тут происходит? Несколько вещей:

  1. Используя этот шаблон, вы получите одну строку для каждого сообщения, с некоторыми столбцами из таблицы posts и определенным атрибутом из таблицы postmeta .
  2. Вы находитесь в таблице postmeta LEFT JOIN так что вы все равно получаете строку, если атрибут отсутствует.
  3. Вы используете псевдоним для таблицы postmeta . Здесь это postmeta AS color после postmeta AS color .
  4. Вы включаете селектор для meta_key (здесь это « 'color' = color.meta_key ) в состоянии ON соединения.
  5. Вы используете псевдоним в своем предложении SELECT , чтобы представить элемент postmeta.meta_value с соответствующим именем столбца. Здесь color.meta_value AS color .

Как только вы привыкнете к использованию этого шаблона, вы можете складывать его с помощью каскада операций LEFT JOIN , чтобы получить множество разных атрибутов, например.

  SELECT wp_posts.ID, wp_posts.post_title, wp_posts.whatever, color.meta_value AS color, transmission.meta_value AS transmission, model.meta_value AS model, brand.meta_value AS brand FROM wp_posts LEFT JOIN wp_postmeta AS color ON wp_posts.ID = color.post_id AND color.meta_key='color' LEFT JOIN wp_postmeta AS transmission ON wp_posts.ID = transmission.post_id AND transmission.meta_key='transmission' LEFT JOIN wp_postmeta AS model ON wp_posts.ID = model.post_id AND model.meta_key='model' LEFT JOIN wp_postmeta AS brand ON wp_posts.ID = brand.post_id AND brand.meta_key='brand' WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'car' ORDER BY wp_posts.post_title 

Я сделал кучу отступов по этому запросу, чтобы было легче увидеть шаблон. Вы можете выбрать другой отступы.

Трудно понять, почему у вас были проблемы с производительностью с запросом в вашем вопросе. Возможно, потому, что вы получили комбинаторный взрыв со всеми операциями INNER JOIN которые затем были отфильтрованы. Но, во всяком случае, запрос, который вы показали, вероятно, не возвращал строк.

Если у вас все еще есть проблемы с производительностью, попробуйте создать составной индекс в (post_id, meta_key, meta_value) столбцах (post_id, meta_key, meta_value) . Если вы создаете плагин WordPress, это, вероятно, задача, которую нужно сделать во время установки плагина.

Это база данных WordPress, и вы можете неохотно вносить значительные изменения в схему, поскольку она может сломать другие части приложения или усложнить обновления в будущем.

Трудность этого запроса показывает один из недостатков конструкции объекта-атрибута . Этот дизайн является гибким, поскольку он позволяет создавать новые атрибуты во время выполнения, но он делает множество запросов против таких данных более сложными, чем они были бы с обычной таблицей.

Схема для WordPress не была оптимизирована хорошо. Есть некоторые наивные ошибки индексации, даже в самой последней версии 4.0.

Для этого конкретного запроса следующие два индекса помогают:

 CREATE INDEX `bk1` ON wp_postmeta (`post_id`,`meta_key`,`meta_value`(255)); CREATE INDEX `bk2` ON wp_posts (`post_type`,`post_status`,`post_title`(255)); 

Индекс bk1 помогает найти правильный мета-ключ и значение.

Индекс bk2 помогает избежать файлового управления.

Эти индексы не могут покрывать индексы, потому что post_title и meta_value являются столбцами TEXT , и они слишком длинны, чтобы их можно было полностью проиндексировать. Вам придется изменить их на VARCHAR(255) . Но это может нарушить работу приложения, если оно зависит от хранения более длинных строк в этой таблице.

 +----+-------------+--------------+------------+------+---------------+------+---------+----------------------------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+------+---------------+------+---------+----------------------------+------+----------+-----------------------+ | 1 | SIMPLE | wp_posts | NULL | ref | bk2 | bk2 | 124 | const,const | 1 | 100.00 | Using index condition | | 1 | SIMPLE | color | NULL | ref | bk1 | bk1 | 1542 | wp.wp_posts.ID,const,const | 1 | 100.00 | Using index | | 1 | SIMPLE | transmission | NULL | ref | bk1 | bk1 | 1542 | wp.wp_posts.ID,const,const | 1 | 100.00 | Using index | | 1 | SIMPLE | model | NULL | ref | bk1 | bk1 | 1542 | wp.wp_posts.ID,const,const | 1 | 100.00 | Using index | | 1 | SIMPLE | brand | NULL | ref | bk1 | bk1 | 1542 | wp.wp_posts.ID,const,const | 1 | 100.00 | Using index | +----+-------------+--------------+------------+------+---------------+------+---------+----------------------------+------+----------+-----------------------+ 

Чтобы разрешить проблему с производительностью при добавлении 10+ к SQL-запросам в таблицах innodb с использованием кодировки utf8, создайте новый индекс для postmeta:

Сначала резервная база данных. Уменьшите [wp_]postmeta.meta_key до 191, чтобы избежать « Указанная клавиша слишком длинная, максимальная длина ключа – 767 байт ».

ALTER TABLE wp_postmeta MODIFY meta_key VARCHAR(191);

Создать индекс

CREATE INDEX wpm_ix ON wp_postmeta (post_id, meta_key);

Для производительности попробуйте:

Будьте ясны в столбцах, которые вы хотите вытащить. Посмотрите, какие индексы вам могут понадобиться или нет. Ограничьте количество вытягиваемых строк.

это лучше?

 SELECT P.*, C.`meta_value` color, T.`meta_value` transmission, M.`meta_value` model, B.`meta_value` brand FROM `wp_posts` P JOIN `wp_postmeta` C ON P.`ID` = C.`post_id` AND C.`meta_key` = 'color' JOIN `wp_postmeta` T ON P.`ID` = T.`post_id` AND T.`meta_key` = 'transmission' JOIN `wp_postmeta` M ON P.`ID` = M.`post_id` AND M.`meta_key` = 'model' JOIN `wp_postmeta` B ON P.`ID` = B.`post_id` AND B.`meta_key` = 'brand' WHERE C.`meta_value` = 'red' AND T.`meta_value` = 'auto' AND M.`meta_value` = 'model' AND B.`meta_value` = 'brand' AND P.`post_status` = 'publish' AND P.`post_type` = 'car' ORDER BY P.`post_title` 

Если его все еще медленно, что, вероятно, будет, попробуйте добавить этот индекс,

 CREATE INDEX `IX-wp_postmeta-post_id-meta_key-meta_value` ON `wp_postmeta` (`post_id`, `meta_key`, `meta_value`); 

Вы также можете попробовать добавить этот индекс в wp_post

 CREATE UNIQUE INDEX `IX-wp_post-post_status-post_type-post_title-ID` ON `wp_post` (`post_stauts`, `post_type`, `post_title`, `ID`); 

Чем больше вы можете ограничить список выбора (бит между SELECT и FROM ), тем лучше. Нет смысла возвращать много данных, которые вы не будете использовать. Вы получите максимальную производительность, если весь список выбора «покрыт» индексом.

Предполагая, что вы действительно можете изменить код, обрабатывающий результаты, я бы сделал его гораздо более простым и использовал код для фильтрации результатов.

 SELECT [wp_posts fields you care about], wp_postmeta.meta_key, wp_postmeta.meta_value FROM wp_posts INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'car' AND wp_postmeta.meta_key IN ('color', 'transmission', 'model', 'brand') ORDER BY wp_posts.post_title, wp_postmeta.meta_key, wp_postmeta.meta_value; 

Или, вы могли бы сделать что-то вроде …

 SELECT [wp_posts fields desired], COUNT(*) AS matchCount FROM wp_posts INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'car' AND ((meta_key = 'color' AND meta_value = 'red') OR (meta_key = 'transmission' AND meta_value = 'auto') OR [etc...] ) GROUP BY wp_posts.ID HAVING matchCount = [number of key-value pairs you're checking] ; 

В WordPress есть хороший инструмент запроса WP_Query. Чтобы выполнить поиск в сообщениях meta, вы можете использовать этот код:

 $args = array( 'post_type' => 'post', 'meta_query' => array( array( 'key' => 'fieldname', 'value' => 'fieldvalue', 'compare' => 'LIKE', ), ), ); $query = new WP_Query( $args ); if ( $query->have_posts() ) { while ( $query->have_posts() ) { $query->the_post(); $custom = get_post_custom(); print_r($post); print_r($custom); } } else { // no posts found } wp_reset_postdata(); под $args = array( 'post_type' => 'post', 'meta_query' => array( array( 'key' => 'fieldname', 'value' => 'fieldvalue', 'compare' => 'LIKE', ), ), ); $query = new WP_Query( $args ); if ( $query->have_posts() ) { while ( $query->have_posts() ) { $query->the_post(); $custom = get_post_custom(); print_r($post); print_r($custom); } } else { // no posts found } wp_reset_postdata(); 

Для получения дополнительной информации о API запросов посетите этот сайт, есть много примеров: http://codex.wordpress.org/Class_Reference/WP_Query

Ускорение wp_postmeta подробно описано здесь: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

И почему ссылки на wp_postmeta так медленны?