Why is the request taking so long to complete?

Question

MySQL

There are 3 tables: keywords, images, images_keywords

SELECT SQL_NO_CACHE *
FROM keywords
JOIN(
  SELECT DISTINCT keywords.id
  FROM keywords
  JOIN images_keywords ON keywords.id = keyword_id
  JOIN images ON image_id = images.id WHERE images.space = 1
) as ids ON keywords.id = ids.id

Who can explain why this request is executed in 1.6s? The subquery returns ~800 id in 0.0004s, and the number of entries in the keywords table is ~1000.

Unfortunately, I don’t really understand what the data from explain means. I see a temporary table, it seems to be bad, but this is the data of a subquery that is executed instantly.

Answer 1

I do not understand why such a request is built.
Why not just

SELECT k.* --Или лучше перечислить столбцы для выборки
  FROM keywords as k
  JOIN images_keywords ON keywords.id = keyword_id
  JOIN images ON image_id = images.id WHERE images.space = 1

Answer 2

SELECT DISTINCT keywords.id
  FROM keywords
  JOIN images_keywords ON keywords.id = keyword_id
  JOIN images ON image_id = images.id WHERE images.space = 1

For what purpose do you join the keywords table in this subquery?

SELECT DISTINCT keyword_id
  FROM images_keywords
  JOIN images ON image_id = images.id WHERE images.space = 1

The same result will be.
In general, you don’t need a join with a subquery

SELECT SQL_NO_CACHE *
FROM keywords
WHERE keywords.id IN (
  SELECT DISTINCT keyword_id
  FROM images_keywords
  JOIN images ON image_id = images.id WHERE images.space = 1
)

For current versions of mysql, in subquery should work adequately.

Answer 3

m..

SELECT t.*
FROM keywords t
WHERE 1=1
    AND EXISTS( SELECT a.id FROM images_keywords a WHERE t.id = a.keyword_id )
    AND EXISTS( SELECT b.id FROM images b WHERE b.id = t.image_id and b.space =1 )