I have this simple MySQL query:

SELECT * FROM table1
INNER JOIN table2 ON table2.col = table1.id
WHERE table1.id = '123'

id is the primary key in table1.

table2 has composite primary key of two columns: col and col_two.

Now I ran EXPLAIN on the above query to see how it performs and got the following rows:

id select_type table   type  possible_keys  key     key_len  ref    rows Extra
1  SIMPLE      table1  const PRIMARY        PRIMARY 8        const  1
1  SIMPLE      table2  ref   PRIMARY        PRIMARY 8        const  1    Using index

From my understanding, ref would be used if the join matches multiple rows but I'm using WHERE with a constant value. So, shouldn't the join have a const type like the table1 query?

I'm worried that this would cause performance issues on a large scale. Is there a way to improve the above query or it's already good enough?

2 Answers 2

SELECT * FROM table1 INNER JOIN table2
ON table1.id = '123' AND table1.id = table2.col;


(SELECT * FROM table1 WHERE id = '123') A
(SELECT * FROM table2 WHERE col = '123') B
ON B.col = A.id;

The ref isn't referring to a constant, it's comparing two columns from two tables. Using the = operator will result in the ref type for table2.col = table1.id.

For a query this simple, it's hard to recommend any improvements without knowing data sizes, indexes, both table structures, etc. I would say that the SELECT * isn't generally advisable unless you absolutely need to return all columns.

If you have any examples of more complex iterations of the query, then there might be more than can be said.

  • i'm not selecting all cols...this is just for the same of simplicity :) Nov 5, 2014 at 22:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.