What does eq_ref and ref types mean in MySQL explain

Question

When we prefix an SQL query with the keyword "explain" we get a table with some columns. Please tell me what is the "type" column. What does eq_ref and ref mean in that context.

Answer 1

I'll try an explanation...

eq_ref – imagine that you have two tables. Table A with columns (id, text) where id is a primary key. Table B with the same columns (id, text) where id is a primary key. Table A has the following data:

1, Hello  2, How are 

Table B has the following data:

1, world! 2, you? 

Imagine eq_ref as JOIN between A and B:

select A.text, B.text where A.ID = B.ID 

This JOIN is very fast because for each row scanned in table A there can be only ONE row in table B which satisfies the JOIN condition. One and no more than one. That is because B.id is UNIQUE.
Here you are: pseudo code which illustrates the processing at server side:

foreach (rowA in A) {     if (existsInBRowWithID(rowA.id)     {         addToResult(rowA.text, getRowInBWithID(rowA.id).text);     } } 

ref - Now imagine another table C with columns (id, text) in which id an index but a non UNIQUE one. Table C has the following data:

1, John! 1, Jack! 

Imagine ref as JOIN between A and C:

select A.text, C.text where A.ID = C.ID 

Here you are: pseudo code illustrating the server side processing:

foreach (rowA in A) {     foreach (rowC in C)     {         if (rowA.id == rowC.id)         {             addToResult(rowA.text, rowC.text);         }     } } 

This JOIN is NOT as fast as the former one because for each row scanned in table A there are SEVERAL possible rows in table C which may satisfy the JOIN condition (nested loops). That is because C.ID is NOT UNIQUE.

I hope that helps...

Cheerz!