MySQL: WHERE IN any of subqueries

user_1132363 0
Viewed 722

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
);
in-subquery subquery select mysql sql
3 Answers

A quick test on a table integers with only the field i (and 2621441 rows):

SELECT i 
FROM integers
WHERE (
   i in (SELECT i FROM integers WHERE i = 100)
   OR
   i in (SELECT i FROM integers WHERE i = 200)
   OR 
   i in (SELECT i FROM integers WHERE i = 1000)
)
ORDER BY i;

+----+-------------+----------+------------+-------+---------------+---------+---------+-------+---------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows    | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+---------+----------+--------------------------+
|  1 | PRIMARY     | integers | NULL       | index | NULL          | PRIMARY | 4       | NULL  | 2615753 |   100.00 | Using where; Using index |
|  4 | SUBQUERY    | integers | NULL       | const | PRIMARY       | PRIMARY | 4       | const |       1 |   100.00 | Using index              |
|  3 | SUBQUERY    | integers | NULL       | const | PRIMARY       | PRIMARY | 4       | const |       1 |   100.00 | Using index              |
|  2 | SUBQUERY    | integers | NULL       | const | PRIMARY       | PRIMARY | 4       | const |       1 |   100.00 | Using index              |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+---------+----------+--------------------------+
4 rows in set, 1 warning (0.01 sec)

Above returns result in about 2 secs.

SELECT i 
FROM integers
WHERE i in (
   SELECT i FROM integers WHERE i = 100
   UNION ALL
   SELECT i FROM integers WHERE i = 200
   UNION ALL
   SELECT i FROM integers WHERE i = 1000
)
ORDER BY i;

+----+--------------------+----------+------------+-------+---------------+---------+---------+-------+---------+----------+--------------------------+
| id | select_type        | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows    | filtered | Extra                    |
+----+--------------------+----------+------------+-------+---------------+---------+---------+-------+---------+----------+--------------------------+
|  1 | PRIMARY            | integers | NULL       | index | NULL          | PRIMARY | 4       | NULL  | 2615753 |   100.00 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | integers | NULL       | const | PRIMARY       | PRIMARY | 4       | const |       1 |   100.00 | Using index              |
|  3 | DEPENDENT UNION    | integers | NULL       | const | PRIMARY       | PRIMARY | 4       | const |       1 |   100.00 | Using index              |
|  4 | DEPENDENT UNION    | integers | NULL       | const | PRIMARY       | PRIMARY | 4       | const |       1 |   100.00 | Using index              |
+----+--------------------+----------+------------+-------+---------------+---------+---------+-------+---------+----------+--------------------------+
4 rows in set, 1 warning (0.00 sec)

Above returns results in about 1.35 sec

SELECT i 
FROM integers
WHERE i in (
   SELECT i FROM integers WHERE i = 100
   UNION
   SELECT i FROM integers WHERE i = 200
   UNION 
   SELECT i FROM integers WHERE i = 1000
)
ORDER BY i;

+----+--------------------+--------------+------------+-------+---------------+---------+---------+-------+---------+----------+--------------------------+
| id | select_type        | table        | partitions | type  | possible_keys | key     | key_len | ref   | rows    | filtered | Extra                    |
+----+--------------------+--------------+------------+-------+---------------+---------+---------+-------+---------+----------+--------------------------+
|  1 | PRIMARY            | integers     | NULL       | index | NULL          | PRIMARY | 4       | NULL  | 2615753 |   100.00 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | integers     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |       1 |   100.00 | Using index              |
|  3 | DEPENDENT UNION    | integers     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |       1 |   100.00 | Using index              |
|  4 | DEPENDENT UNION    | integers     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |       1 |   100.00 | Using index              |
| NULL | UNION RESULT       | <union2,3,4> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    NULL |     NULL | Using temporary          |
+----+--------------------+--------------+------------+-------+---------------+---------+---------+-------+---------+----------+--------------------------+
5 rows in set, 1 warning (0.00 sec)

Above returns results in 1.6 secs.

The 'winner' is UNION ALL

user_7240390

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)
user_101387340

Actually it can be done with one subquery:

SELECT * 
FROM tbl t1
WHERE t1.id IN (
  SELECT  
    CASE t2.value
        WHEN 100 THEN t2.col1
        WHEN 200 THEN t2.col2
        WHEN 1000 THEN t2.col3
    END AS id
  FROM tbl t2
  WHERE t2.value IN (100, 200, 1000)
)

Tested on 2474003 rows with index v4 on value column:

+----+--------------+-------------+------------+--------+---------------+---------+---------+--------------------+------+----------+-----------------------+
| id | select_type  | table       | partitions | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra                 |
+----+--------------+-------------+------------+--------+---------------+---------+---------+--------------------+------+----------+-----------------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL               | NULL |   100.00 | NULL                  |
|  1 | SIMPLE       | t1          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | <subquery2>.id     |    1 |   100.00 | Using where           |
|  2 | MATERIALIZED | t2          | NULL       | range  | v4            | v4      | 4       | NULL               |    7 |   100.00 | Using index condition |
+----+--------------+-------------+------------+--------+---------------+---------+---------+--------------------+------+----------+-----------------------+

Or without subqueries. If col1, col2, and col3 are a subset of id, then the subquery is the answer itself.

user_147172380
Related