Простой, но медленный запрос в Mysql, что соптимизить

Question

[DexizeR]

Запрос примитивный — вытащить посты по указанному тегу

SELECT SQL_NO_CACHE posts.id, posts.text, posts.anno, posts.date
FROM `posts`
INNER JOIN `posts_xref_tags` ON posts_xref_tags.post_id = posts.id
WHERE posts_xref_tags.tag_id = 1
ORDER BY posts.date DESC
LIMIT 0,10

в posts primary на id, индекс на date

в posts_xref_tags составной primary на tag_id + post_id и индекс на tag_id

Постов в базе 30000, перекрестных связей 40000… постов имеющих связь с tag_id = 1 4,281

запрос выполняется за 0.2с

explain:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	posts_xref_tags	ref	PRIMARY,tag_id	tag_id	4	const	4244	Using temporary; Using filesort
1	SIMPLE	posts	eq_ref	PRIMARY	PRIMARY	4	db_site. posts_xref_tags. post_id	1

UPD: Нашел решение!!!

SELECT SQL_NO_CACHE posts.id, posts.text, posts.anno, posts.create_date
FROM `az_posts`
WHERE id
IN (

SELECT `post_id`
FROM `posts_xref_tags`
WHERE tag_id =1
)
ORDER BY posts.create_date DESC
LIMIT 0, 10

выполняется за 0.002с
всем спасибо!

Comments

[DexizeR]

SQL_NO_CACHE используется только для замера производительности, в продакшне не используется

Answer 1

[DexizeR]

Нашел решение!!!
SELECT SQL_NO_CACHE p.id, p.text, p.anno, p.create_date
FROM `az_posts` p
WHERE id
IN (

SELECT `post_id`
FROM az_posts_tags
WHERE tag_id =1
)
ORDER BY p.create_date DESC
LIMIT 0, 10

выполняется за 0.002с
всем спасибо!

Answer 2

[IlVin]

SELECT p.id, p.text, p.anno, p.date
FROM `posts` p
INNER JOIN
( SELECT distinct `post_id`
FROM posts_xref_tags
WHERE tag_id = 1 ) AS pids ON (p.id=pids.post_id)
ORDER BY p.date DESC
LIMIT 0,10

Comments

[DexizeR]

еще медленнее работает, 0.3с выдает…

[IlVin]

А этот позапрос за сколько времени выполняется?
SELECT distinct `post_id`
FROM posts_xref_tags
WHERE tag_id = 1

[DexizeR]

ваша идея натолкнула меня на правильный ответ — см. ниже
спасибо!

[IlVin]

Это было бы следующим шагом :)

[DexizeR]

поставил плюсик вам плюсик в карму ,)

[IlVin]

Спасибо :)

Answer 3

[SCode]

P.S. Индексы — очень важная вещь. И продумывать их надо до конструирования таблицы.

Answer 4

[Павел Чипак]

Сделайте двойной индекс на tag_id и date.

Comments

[Павел Чипак]

Черт, не заметил что выборка из разных таблиц…

Answer 5

[mitnlag]

Без Order и Limit вероятнее всего не будет использоваться temprorary, что создает временную таблицу НА ДИСКЕ. Таким образом, нужно либо убрать SQL_NO_CACHE, чтобы воспользоваться нормальным механизмом, либо исключить создание файлов, убрав order и limit, либо поместить /tmp на SSD.

Comments

[DexizeR]

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