MySQL improve query syntax

Question

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?

Answer 1

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;

Answer 2

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.

• Michael Samuel: i'm not selecting all cols...this is just for the same of simplicity :)