MySQL improve query syntax

join;MySQLperformance

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?

Best Answer

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

or

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