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

вам точно нужен LEFT JOIN, а не простой INNER JOIN?

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

mysqltuner что на ваш конфиг говорит?

Из того, что сразу бросилось в глаза: (думаю mysqltuner это подтвердит)

IMHO, 100 коннектов - сильно с запасом, хватит и 50

64M на кеш запросов - очень мало, выделите хотя бы 192M (если память позволяет, то и больше)

Ничего.

Mysql не сломан. Вы не в том направлении думаете. Mysql делает работу по считыванию и объединению данных, которую вы ему задали.

Вам нужно изменить запрос.

А что изменить в запросе?

Пробовал менять на inner join не сильно помогло,

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

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

SQL-запрос: EXPLAIN SELECT SQL_NO_CACHE wposts . * FROM wp_posts wposts LEFT JOIN wp_term_relationships ON ( wposts.ID = wp_term_relationships.object_id ) LEFT JOIN wp_term_taxonomy ON ( wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id ) WHERE wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id = '49' AND wposts.post_status = 'publish' AND wposts.post_type = 'post' AND wposts.ID < '2756199' ORDER BY wposts.ID DESC LIMIT 4;

Строки: 3

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE wp_term_taxonomy const PRIMARY,term_id_taxonomy,taxonomy term_id_taxonomy 44 const,const 1 Using temporary; Using filesort

1 SIMPLE wp_term_relationships ref PRIMARY,term_taxonomy_id term_taxonomy_id 2 const 77350 Using where

1 SIMPLE wposts eq_ref PRIMARY,type_status_id_date PRIMARY 3 site.wp_term_relationships.object_id 1 Using where

запрос занял 0.5007 сек.

SQL-запрос: EXPLAIN SELECT SQL_NO_CACHE wposts . * FROM wp_posts wposts IGNORE INDEX (PRIMARY,type_status_id_date) LEFT JOIN wp_term_relationships ON ( wposts.ID = wp_term_relationships.object_id ) LEFT JOIN wp_term_taxonomy ON ( wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id ) WHERE wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id = '49' AND wposts.post_status = 'publish' AND wposts.post_type = 'post' AND wposts.ID < '2756199' ORDER BY wposts.ID DESC LIMIT 4;

Строки: 3

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE wp_term_taxonomy const PRIMARY,term_id_taxonomy,taxonomy term_id_taxonomy 44 const,const 1 Using filesort

1 SIMPLE wposts ALL NULL NULL NULL NULL 337241 Using where

1 SIMPLE wp_term_relationships eq_ref PRIMARY,term_taxonomy_id PRIMARY 5 site.wposts.ID,const 1 Using where; Using index

А если убрать ORDER BY, сильно изменится время выполнения? Если да, то крутите sort_buffer_size или думайте над размещением временных файлов для сортировки на SSD.

запрос занял 0.0214 сек.

SELECT SQL_NO_CACHE wposts. *

FROM wp_posts wposts

LEFT JOIN wp_term_relationships ON ( wposts.ID = wp_term_relationships.object_id )

LEFT JOIN wp_term_taxonomy ON ( wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id )

WHERE wp_term_taxonomy.taxonomy = 'category'

AND wp_term_taxonomy.term_id = '49'

AND wposts.post_status = 'publish'

AND wposts.post_type = 'post'

AND wposts.ID < '2756199'

LIMIT 4

попробую sort_buffer_size поднять, SSD для меня не вариант, на моей впске нет такой возможности, переходить на дедик из за 1 запроса тоже как то не хочется.

Кеширование по времени создать не вариант? Запрос срабатывает при каждом обращении к странице?

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

вот сам скрипт который вызывает этот запрос:

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

$num = 0;

global $wpdb;

$sql = "SELECT wposts.*

FROM $wpdb->posts wposts IGNORE INDEX (PRIMARY,type_status_id_date)

LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id)

LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)

WHERE $wpdb->term_taxonomy.taxonomy = 'category'

AND $wpdb->term_taxonomy.term_id = '$the_category_id'

AND wposts.post_status = 'publish'

AND wposts.post_type = 'post'

AND wposts.ID < '$the_post_id'

ORDER BY wposts.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;

$more_posts = get_posts("numberposts=$need_more&category=$the_category_id");

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]->cat_ID; show_previous_posts_from_category($post->ID, $the_cat_id, 4); wp_reset_query(); ?>

Все равно многовато. Теперь еще LEFT JOIN -> INNER JOIN сделайте и покажите план выполнения.

c order by

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

SQL-запрос: EXPLAIN SELECT SQL_NO_CACHE wposts . * FROM wp_posts wposts INNER JOIN wp_term_relationships ON ( wposts.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 wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id = '49' AND wposts.post_status = 'publish' AND wposts.post_type = 'post' AND wposts.ID < '2756199' ORDER BY wposts.ID DESC LIMIT 4;

Строки: 3

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE wp_term_taxonomy const PRIMARY,term_id_taxonomy,taxonomy term_id_taxonomy 44 const,const 1 Using temporary; Using filesort

1 SIMPLE wp_term_relationships ref PRIMARY,term_taxonomy_id term_taxonomy_id 2 const 79416 Using where

1 SIMPLE wposts eq_ref PRIMARY,type_status_id_date PRIMARY 3 site.wp_term_relationships.object_id 1 Using where

без order by

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

SQL-запрос: EXPLAIN SELECT SQL_NO_CACHE wposts . * FROM wp_posts wposts INNER JOIN wp_term_relationships ON ( wposts.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 wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id = '49' AND wposts.post_status = 'publish' AND wposts.post_type = 'post' AND wposts.ID < '2756199' LIMIT 4;

Строки: 3

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE wp_term_taxonomy const PRIMARY,term_id_taxonomy,taxonomy term_id_taxonomy 44 const,const 1

1 SIMPLE wp_term_relationships ref PRIMARY,term_taxonomy_id term_taxonomy_id 2 const 79416 Using where

1 SIMPLE wposts eq_ref PRIMARY,type_status_id_date PRIMARY 3 site.wp_term_relationships.object_id 1 Using where

вывод из mysqltuner

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.1.71

[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 688M (Tables: 20)

[!!] Total fragmented tables: 1

-------- Performance Metrics -------------------------------------------------

[--] Up for: 18h 23m 57s (780K q [11.782 qps], 56K conn, TX: 6B, RX: 168M)

[--] Reads / Writes: 54% / 46%

[--] Total buffers: 144.0M global + 17.2M per thread (100 max threads)

[OK] Maximum possible memory usage: 1.8G (21% of installed RAM)

[OK] Slow queries: 0% (1/780K)

[OK] Highest usage of available connections: 19% (19/100)

[OK] Key buffer size / total MyISAM indexes: 64.0M/40.5M

[OK] Key buffer hit rate: 99.9% (476M cached / 435K reads)

[OK] Query cache efficiency: 73.1% (361K cached / 494K selects)

[!!] Query cache prunes per day: 15565

[OK] Sorts requiring temporary tables: 1% (1K temp sorts / 82K sorts)

[OK] Temporary tables created on disk: 5% (2K on disk / 40K total)

[OK] Thread cache hit rate: 99% (19 created / 56K connections)

[!!] Table cache hit rate: 15% (61 open / 389 opened)

[OK] Open file limit used: 0% (90/24K)

[OK] Table locks acquired immediately: 99% (365K immediate / 365K locks)

-------- Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

MySQL started within last 24 hours - recommendations may be inaccurate

Enable the slow query log to troubleshoot bad queries

Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

query_cache_size (> 64M)

table_cache (> 512)

из tuning-primer

-- MYSQL PERFORMANCE TUNING PRIMER --

- By: Matthew Montgomery -

MySQL Version 5.1.71 amd64

Uptime = 0 days 18 hrs 28 min 0 sec

Avg. qps = 11

Total Questions = 782585

Threads Connected = 2

Warning: Server has not been running for at least 48hrs.

It may not be safe to use these recommendations

To find out more information on how each of these

runtime variables effects performance visit:

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html

Visit http://www.mysql.com/products/enterprise/advisors.html

for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES

The slow query log is NOT enabled.

Current long_query_time = 10.000000 sec.

You have 1 out of 782606 that take longer than 10.000000 sec. to complete

Your long_query_time seems to be fine

BINARY UPDATE LOG

The binary update log is NOT enabled.

You will not be able to do point in time recovery

See http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html

WORKER THREADS

Current thread_cache_size = 16

Current threads_cached = 15

Current threads_per_sec = 0

Historic threads_per_sec = 0

Your thread_cache_size is fine

MAX CONNECTIONS

Current max_connections = 100

Current threads_connected = 2

Historic max_used_connections = 19

The number of used connections is 19% of the configured maximum.

Your max_connections variable seems to be fine.

No InnoDB Support Enabled!

MEMORY USAGE

Max Memory Ever Allocated : 455 M

Configured Max Per-thread Buffers : 1.68 G

Configured Max Global Buffers : 128 M

Configured Max Memory Limit : 1.80 G

Physical Memory : 8.49 G

Max memory limit seem to be within acceptable norms

KEY BUFFER

Current MyISAM index space = 40 M

Current key_buffer_size = 64 M

Key cache miss rate is 1 : 1094

Key buffer free ratio = 52 %

Your key_buffer_size seems to be fine

QUERY CACHE

Query cache is enabled

Current query_cache_size = 64 M

Current query_cache_used = 53 M

Current query_cache_limit = 1 M

Current Query cache Memory fill ratio = 83.69 %

Current query_cache_min_res_unit = 4 K

However, 11933 queries have been removed from the query cache due to lack of memory

Perhaps you should raise query_cache_size

MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS

Current sort_buffer_size = 6 M

Current read_rnd_buffer_size = 6 M

Sort buffer seems to be fine

JOINS

Current join_buffer_size = 1.00 M

You have had 0 queries where a join could not use an index properly

Your joins seem to be using indexes properly

OPEN FILES LIMIT

Current open_files_limit = 24576 files

The open_files_limit should typically be set to at least 2x-3x

that of table_cache if you have heavy MyISAM usage.

Your open_files_limit value seems to be fine

TABLE CACHE

Current table_open_cache = 512 tables

Current table_definition_cache = 256 tables

You have a total of 20 tables

You have 61 open tables.

The table_cache value seems to be fine

TEMP TABLES

Current max_heap_table_size = 16 M

Current tmp_table_size = 16 M

Of 38901 temp tables, 5% were created on disk

Created disk tmp tables ratio seems fine

TABLE SCANS

Current read_buffer_size = 4 M

Current table scan ratio = 58007 : 1

read_buffer_size seems to be fine

TABLE LOCKING

Current Lock Wait ratio = 1 : 555

You may benefit from selective use of InnoDB.

память Physical Memory : 8.49 G показывает общую серверную, на впс выделено 2гб