sql - MySQL: WHERE IN any of subqueries

Question

How to restructure this query:

SELECT * FROM tbl t
WHERE (
       t.id IN <subquery1>
    OR t.id IN <subquery2>
    OR t.id IN <subquery3>
)

... into something that looks more like the following:

SELECT * FROM tbl t
WHERE t.id IN (<subquery1> OR <subquery2> OR <subquery3>)

Note: all 3 subqueries select from the same tbl t, but they select a different column each.

To clarify the subqueries a bit further with some concrete examples:

• subquery1: SELECT col1 FROM tbl WHERE value=100
• subquery2: SELECT col2 FROM tbl WHERE value=200
• subquery3: SELECT col3 FROM tbl WHERE value=300

Table structure:

CREATE TABLE tbl (
    id      INTEGER   PRIMARY KEY,
    col1    INTEGER   not null,
    col2    INTEGER   not null,
    col3    INTEGER   not null,
    value   INTEGER   not null
);

question from:https://stackoverflow.com/questions/65897478/mysql-where-in-any-of-subqueries

Answer 1

I have tested a lot of variants (synthetic table, 10kk rows, colX = random in 1..10kk, value = random in 1..1kk). The most fast is:

CREATE INDEX idx ON test (value);

SELECT id
FROM test
WHERE id in (SELECT col1 FROM test WHERE value = 100)
UNION
SELECT id
FROM test
WHERE id in (SELECT col2 FROM test WHERE value = 200)
UNION
SELECT id
FROM test
WHERE id in (SELECT col3 FROM test WHERE value = 1000)
ORDER BY id;

---

mysql> SELECT id
    -> FROM test
    -> WHERE id in (SELECT col1 FROM test WHERE value = 100)
    -> UNION
    -> SELECT id
    -> FROM test
    -> WHERE id in (SELECT col2 FROM test WHERE value = 200)
    -> UNION
    -> SELECT id
    -> FROM test
    -> WHERE id in (SELECT col3 FROM test WHERE value = 1000)
    -> ORDER BY id;
-- <output skipped>
36 rows in set (1.60 sec)

mysql> SELECT id
    -> FROM test
    -> WHERE (
    ->    id in (SELECT col1 FROM test WHERE value = 100)
    ->    OR
    ->    id in (SELECT col2 FROM test WHERE value = 200)
    ->    OR
    ->    id in (SELECT col3 FROM test WHERE value = 1000)
    -> )
    -> ORDER BY id;
-- <output skipped>
36 rows in set (29.18 sec)