Using SELECT within SELECT in mysql query

Question

It is common to use SELECT within SELECT to reduce the number of queries; but as I examined this leads to slow query (which is obviously harmful for mysql performance). I had a simple query as

SELECT something
 FROM posts
 WHERE id IN (
  SELECT tag_map.id
  FROM tag_map
  INNER JOIN tags
  ON tags.tag_id=tag_map.tag_id
  WHERE tag IN ('tag1', 'tag2', 'tag3', 'tag4', 'tag5', 'tag6')
  )

This leads to slow queries of "query time 3-4s; lock time about 0.000090s; with about 200 rows examined".

If I split the SELECT queries, each of them will be quite fast; but this will increase the number of queries which is not good at high concurrency.

Is it the usual situation, or something is wrong with my coding?

Solution 1

In MySQL, doing a subquery like this is a "correlated query". This means that the results of the outer SELECT depend on the result of the inner SELECT. The outcome is that your inner query is executed once per row, which is very slow.

You should refactor this query; whether you join twice or use two queries is mostly irrelevant. Joining twice would give you:

SELECT something
FROM posts
INNER JOIN tag_map ON tag_map.id = posts.id
INNER JOIN tags ON tags.tag_id = tag_map.tag_id
WHERE tags.tag IN ('tag1', ...)

For more information, see the MySQL manual on converting subqueries to JOINs.

Tip: EXPLAIN SELECT will show you how the optimizer plans on handling your query. If you see DEPENDENT SUBQUERY you should refactor, these are mega-slow.

Solution 2

You could improve it by using the following:

SELECT something
FROM posts
INNER JOIN tag_map ON tag_map.id = posts.id
INNER JOIN tags
ON tags.tag_id=tag_map.tag_id
WHERE <tablename>.tag IN ('tag1', 'tag2', 'tag3', 'tag4', 'tag5', 'tag6')

Just make sure you only select what you need and do not use *; also state in which table you have the tag column so you can substitute <tablename>

Solution 3

Join does filtering of results. First join will keep results having 1st ON condition satisfied and then 2nd condition gives final result on 2nd ON condition.

SELECT something
FROM posts
INNER JOIN tag_map ON tag_map.id = posts.id
INNER JOIN tags ON tags.tag_id = tag_map.tag_id AND tags.tag IN ('tag1', 'tag2', 'tag3', 'tag4', 'tag5', 'tag6');

You can see these discussions on stack overflow :

question1 question2

Join helps to decrease time complexity and increases stability of server.

Information for converting sub queries to joins:

link1 link2 link3

Comments

[Harry Joy]

This is what we call JOIN.

[jclozano]

yup, inner join twice, at least i think its faster than the in clause