sql - Mysql multi-column indexes approach when table used twice in one query - Stack Overflow

admin管理员组

文章数量:1125786

I have mysql query that looks kinda like this

SELECT * FROM table1
WHERE table1.column_1 = 'a' AND table1.column_2 = 'b'
AND :idList IN 
(SELECT id FROM table1 WHERE table1.column_3 = 'c' AND table1.column_4 = 'd')

:idList is a parameter passed in a function where this query is written

I need to add indexes to table1 to speedup this operation, because table1 is huge and doesn't have any indexes on used columns. If I understand this correct, after adding two indexes (column_1, column_2) and (column_3, column_4), only one of them will be used.
What would be correct approach for adding indexes here? Options I tried so far:

1. Add index on (column_1, column_2). Run query. Drop index and add another on (column_3, column_4). Run query. Stop on fastest variant
2. Add index on (column_1, column_2, column_3, column_4)

I am aware of EXPLAIN but it hasn't been too helpful in this particular case so far

I have mysql query that looks kinda like this

SELECT * FROM table1
WHERE table1.column_1 = 'a' AND table1.column_2 = 'b'
AND :idList IN 
(SELECT id FROM table1 WHERE table1.column_3 = 'c' AND table1.column_4 = 'd')

:idList is a parameter passed in a function where this query is written

I need to add indexes to table1 to speedup this operation, because table1 is huge and doesn't have any indexes on used columns. If I understand this correct, after adding two indexes (column_1, column_2) and (column_3, column_4), only one of them will be used.
What would be correct approach for adding indexes here? Options I tried so far:

1. Add index on (column_1, column_2). Run query. Drop index and add another on (column_3, column_4). Run query. Stop on fastest variant
2. Add index on (column_1, column_2, column_3, column_4)

I am aware of EXPLAIN but it hasn't been too helpful in this particular case so far

• sql
• mysql
• indexing

Share Improve this question Follow edited 2 days ago LokiTheCreator asked 2 days ago LokiTheCreatorLokiTheCreator 3144 bronze badges 2

• 1 In your query the table2.id IN makes no sense, because there is no second table mentioned there. Please clarify your code. – Tim Biegeleisen Commented 2 days ago
• The subquery is independent. So it must be optimized separately. It is used in WHERE and checks for a row presence - so it must be converted to WHERE EXISTS and the parameter must be moved into its WHERE. Accordingly the index by (column_3, column_4, id) will optimize the subquery. And the index by (column_1, column_2) will optimize outer query. You tell that this is a code within the function - transform it to IF EXISTS (your subquery) THEN (your outer query). – Akina Commented 2 days ago

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You said:

If I understand this correct, after adding two indexes (column_1, column_2) and (column_3, column_4), only one of them will be used.

In fact, two separate indices on (column_1, column_2) and (column_3, column_4) should help the performance. The first index can be used in the main query, while the second one can be used in the subquery. It should be noted that in MySQL, running on InnoDB, the id column would effectively be added to the end of the two indices, meaning the first index would really behave as (column_1, column_2, id) and the same for the second index.

Given that the main query and subquery are separate steps in the query pipeline, there is no reason why MySQL cannot both indices in the same query. So, the tuning you have now is already good, and you should verify this by running:

EXPLAIN ANALYZE
SELECT *
FROM table1 t1
WHERE column_1 = 'a' AND column_2 = 'b' AND
    id IN (
        SELECT id
        FROM table1 t2
        WHERE t2.column_3 = 'c' AND t2.column_4 = 'd
    );

The output from the above will reveal to you if the indices you defined are being used.

本文标签： sqlMysql multicolumn indexes approach when table used twice in one queryStack Overflow