Can the following query be optimized? What indexes can be created?
SELECT column_a
FROM Table_b
JOIN Table_a
WHERE Table_B.ID_b = Table_A.ID_a
OR Table_B.ID_b = Table_A.ID_b;
Add a comment
|
2 Answers
Your query should actually be:
SELECT column_a
FROM Table_b
JOIN Table_a ON Table_B.ID_b IN (Table_A.ID_a, Table_A.ID_b)
If you don't provide ON criteria with the JOIN, MySQL accepts this as being a CROSS JOIN -- the result is a cartesian product (that's bad, unless that's really what you want). If I knew which table that column_a came from, I might suggest a different approach to the query...
Index the following:
- Table_B.ID_b
- Table_A.ID_a
- Table_A.ID_b
The two columns in TABLE_A could be a covering index, rather than separate ones.
2 Comments
Se D
Tried the above but the explain did not change
OMG Ponies
@Se D: Without seeing actual data, there's not much to work with -- sorry.
If the ID_x fields are keys (primary or unique), this should already be pretty good. (I.e., if they're not, you should make sure that all fields affected by the WHERE part are indexed.)
Consider posting an EXPLAIN of the query:
EXPLAIN SELECT column_a FROM Table_b JOIN Table_a
WHERE Table_B.ID_b = Table_A.ID_a OR Table_B.ID_b = Table_A.ID_b;
From comments:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | Table_b | index | INDEX_ON_ID_b | INDEX_ON_ID_b | 3 | NULL | 1507 | Using index; Using temporary |
| 1 | SIMPLE | Table_a | ALL |ID_a,ID_b,ID_a_column_a, ID_b_column_a_index | NULL | NULL | NULL | 29252089 | Range checked for each record (index map: 0x306) |
7 Comments
Se D
Explain output - | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ----------+ | 1 | SIMPLE | Table_b | index | INDEX_ON_ID_b | INDEX_ON_ID_b | 3 | NULL | 1507 | Using index; Using temporary | | 1 | SIMPLE | Table_a | ALL |ID_a,ID_b,ID_a_column_a, ID_b_column_a_index | NULL | NULL | NULL | 29252089 | Range checked for each record (index map: 0x306) |
Se D
@jensgram - Explain output - | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ----------+ | 1 | SIMPLE | Table_b | index | INDEX_ON_ID_b | INDEX_ON_ID_b | 3 | NULL | 1507 | Using index; Using temporary | | 1 | SIMPLE | Table_a | ALL |ID_a,ID_b,ID_a_column_a, ID_b_column_a_index | NULL | NULL | NULL | 29252089 | Range checked for each record (index map: 0x306
Se D
@jensgram - Any more suggestions?
jensgram
@Se D As I read it, there are no suitable indexes on
Table_a. Are there any indexes defined for that table? (Please post these, if any, on your question.)Se D
@jensgram | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | 1 | SIMPLE | Table_b | index | INDEX_ON_ID_b | INDEX_ON_ID_b | 3 | NULL | 1507 | Using index; Using temporary | | 1 | SIMPLE | Table_a | ALL |ID_a,ID_b,ID_a_column_a, ID_b_column_a_index | NULL | NULL | NULL | 29252089 | Range checked for each record (index map: 0x306) |
|