оптимизация sql запроса

Mister_Black, настройки нормальные. Что вы к ним прикопались ?

Тут нужно погружение в этот плагин. Разобраться, что там за данные. Доступ к ним для экспериментов. Выяснить на какие компромиссы вы готовы пойти. Все обсуждать и согласовывать с вами. Кто это все спонсировать будет ?

Выключите эту навигацию. Или в гугле найдите похожий проблемный запрос и методы решения. Все же это WP и проблемы с ним редко бывают уникальными. (Я, однако, не нашел ) .

А это полезный совет от mysqltuner. Стоит включить с порогом в районе 1 секунды чтобы определять запросы, на которые нужно обратить внимание в первую очередь.

like ещё притормаживает иногда, но я это решил ограничив поиск то title, если отключить скрипт предыдущих постов из категории, то всё летает, а при включенном тормозит и растёт нагрузка, может есть смысл разбить его на 2 запроса, чтобы один запрос делал выборку по id, а второй запрос уже брал остальную информацию из выделенных по id записей в данном случае 4 записи, у меня по такому принципу сделана выборка категорий, один запрос берёт id ограниченные 10 постами на странице, а второй запрос вытягивает остальное уже из этих 10 не перетряхивая все посты в категории, раньше тоже жутко тормозило, вроде эту проблему решили аж только в 4 версии WP, она возникала на блогах с большим количеством постов, более 10к.

Надо подумать как теперь этот метод использовать в этом скрипте.

Вот, это уже нормально, быстро. Видно, что тормоза у вас идут на "Using temporary; Using filesort". Теперь уберите лишние столбцы, которые не нужны, из условия запроса (вместо wposts .* перечислите конкретные столбцы, которые используются в коде; если используются все - то можете оставить *), уберите из запроса "IGNORE INDEX (PRIMARY,type_status_id_date)" и увеличьте параметры sort_buffer_size и read_rnd_buffer_size для начала в 2 раза.

Изменится ли результат с order by?

я тут переписал немного запрос

SELECT SQL_NO_CACHE p.ID, post_date, post_content, post_title, post_name

FROM wp_posts p

WHERE 1=1 AND ( p.ID IN (

SELECT object_id

FROM wp_term_relationships

WHERE term_taxonomy_id IN ('9')

) )

AND p.ID < '2756199'

ORDER BY p.ID DESC

LIMIT 4

запрос занял 0.0005 сек

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY p range PRIMARY PRIMARY 3 NULL 328324 Using where

2 DEPENDENT SUBQUERY wp_term_relationships unique_subquery PRIMARY,term_taxonomy_id PRIMARY 5 func,const 1 Using index; Using where

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

Ну это уже совсем другой запрос, теперь таблица wp_term_taxonomy в нем не участвует, кортежи должны быть разные теперь. Не из-за этого?

Вот такой получился скрипт, возможно ещё какие то косяки вылезут, но это ужа завтра разберусь, главное что нагрузку снял и кольцевую перелинковку восстановил

<?php function show_previous_posts_from_category ($the_post_id, $the_category_id = 0, $post_num) {

$num = 0;

global $wpdb;

$sql = "SELECT p.ID, post_date, post_content, post_title, post_name 

FROM $wpdb->posts  p

WHERE 1=1  AND (p.ID IN(

SELECT object_id

FROM $wpdb->term_relationships

WHERE term_taxonomy_id = '$the_category_id'

))

AND p.ID < '$the_post_id'

ORDER BY p.ID DESC

LIMIT $post_num";

$result = $wpdb->get_results($sql, OBJECT);

global $post;

foreach ($result as $post) {

setup_postdata($post);

?>

<div class="related"><a href="<?php the_permalink() ?>" title="<?php the_title(); ?>"><img src="<?php echo catch_that_image() ?>" alt="<?php the_title(); ?>" height="115" width="150"></a><b><?php the_title(); ?></b></div>

<?php

$num++;

}

if ( $num < $post_num || !$result ) {

$need_more = $post_num-$num;

$sql = "SELECT p.ID, post_date, post_content, post_title, post_name 

FROM $wpdb->posts  p

WHERE 1=1  AND (p.ID IN(

SELECT object_id

FROM $wpdb->term_relationships

WHERE term_taxonomy_id = '$the_category_id'

))

AND p.ID > '$the_post_id'

ORDER BY p.ID DESC

LIMIT $need_more";

$more_posts = $wpdb->get_results($sql, OBJECT);

foreach ($more_posts as $post){

setup_postdata($post);

?>

<div class="related"><a href="<?php the_permalink() ?>" title="<?php the_title(); ?>"><img src="<?php echo catch_that_image() ?>" alt="<?php the_title(); ?>" height="115" width="150"></a><b><?php the_title(); ?></b></div>

<?php }}} ?>

<?php $the_cat = get_the_category(); $the_cat_id = $the_cat[0]->term_taxonomy_id; show_previous_posts_from_category($post->ID, $the_cat_id, 4); wp_reset_query(); ?>

Возникла такая проблемка, ночью когда самая минимальная нагрузка, начинают виснуть запросы использующие Using temporary, Using filesort

например:

SELECT wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('5124') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 60, 10;

# Query_time: 23.347769 Lock_time: 0.000065 Rows_sent: 10 Rows_examined: 12216

SELECT wp_posts.ID FROM wp_posts WHERE 1=1 AND ((wp_posts.post_title LIKE '%blah%') AND (wp_posts.post_title LIKE '%blah%') OR (wp_posts.post_title LIKE '%blah blah%')) AND (wp_posts.post_status = 'publish') ORDER BY wp_posts.post_date DESC LIMIT 10, 10;

# Query_time: 12.691927 Lock_time: 0.000079 Rows_sent: 10 Rows_examined: 338874

Это началось после того как я убрал нагрузку изменив тот запрос, если вернуть нагрузку, то всё нормально, если можно так сказать :)

Что это может быть?

Недостаточно внимательные измерения.

Совпадения и сделанные из них неверные выводы.

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

Расстрэлят.

тоже думал на счёт бэкапа вэпээсок, возможно увеличивается дисковая нагрузка на основном сервере в это время.

Mister_Black, нууу так может быть настало время завести мониторинг, чтобы видеть когда бекап слегка вмешивается, а когда запросы действительно мешают нормальным пользователями ? Для начала рекомендую munin.

Так же почитайте как и почему пользоваться pt-query-digest . Это позволит сосредоточиться на действительно важных запросах.

Конечно, запрос с характеристикой Rows_examined : 338874 наверняка проблемный и им стоит заняться. Но вы же хотели чтобы вам объяснили парадокс : запрос вроде исправляется, но становится только хуже. Вот я объяснил.