Mysql IN() vs UNION ALL performance

Question

Can someone explain the difference in performance characteristics of the following two queries if any?

Query 1

SELECT id FROM users WHERE status IN(1, 6, 11, 13);

Query 2

(SELECT id FROM users WHERE status = 1)
UNION ALL
(SELECT id FROM users WHERE status = 6)
UNION ALL
(SELECT id FROM users WHERE status = 11)
UNION ALL
(SELECT id FROM users WHERE status = 13)

I am aware that Query #1 is far more legible to a human.

I am interested in a discussion about how their performance characteristics might be the same or different. You can assume that there is an index on users.status

Answer 1

With an index, the two should be essentially equivalent from a performance perspective. The basic process when an index is available is an index lookup to get the matching rows and then fetching the data pages. The first does this four times within a single query unit. The second does four query units on one id.

Without an index, the first should be faster -- up to four times faster if the table needs to be read from disk each time. Or just incrementally faster if the table fits in available memory and the subsequent scans are using a warm data page cache.

Answer 2

In second query, you are querying your table separately. For each select it has to read a table. In first approach it has to read the table once.

With an index, performance won't be much different.

Without an index, each table read would have to be full table scan and difference in performances between those two approaches will be even bigger.

Thus, first query will be faster.