Simple but slow query in Mysql, what to optimize

Question

MySQL

The query is primitive - pull out posts by the specified tag

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

in posts primary on id, index on date

in posts_xref_tags composite primary on tag_id + post_id and index on tag_id

Posts in base 30000, cross-links 40000… posts related to tag_id = 1 4,281

query runs in 0.2 with

explain:

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

UPD : Found a solution!!!

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

runs in 0.002s
Thank you all!

Answer 1

Found a solution!!!
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 costs
0.002 with
all thanks!

Answer 2

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

Answer 3

PS Indexes are a very important thing. And it is necessary to think over them before designing the table.

Answer 4

Make a double index on tag_id and date.

Answer 5

Without Order and Limit, temprorary will most likely not be used, which creates a temporary table ON DISK. Thus, you need to either remove SQL_NO_CACHE to use the normal mechanism, or exclude file creation by removing order and limit, or put /tmp on the SSD.