CODEWITHSUNDEEP
mysqlsql
Se D
Se D

Reputation: 1

mysql query with OR optimization

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;

Upvotes: 0

Views: 83

Answers (2)

jensgram
jensgram

Reputation: 31518

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

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332751

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.

Upvotes: 2

Related Questions