Geek Answers Handbook

What do the values ​​of eq_ref and ref mean in MySQL?

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

+6
optimization database mysql explain
source share
2 answers

I will try the explanation ...

eq_ref - Imagine you have two tables. Table A with columns (id, text), where id is the primary key. Table B with the same columns (id, text), where id is the primary key. Table A contains the following data:

 1, Hello 2, How are

Table B contains the following data:

 1, world! 2, you?

Think of eq_ref as the CONNECTION between A and B:

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

This JOIN is very fast because for every row checked in table A, in table B there can only be an ONE row that satisfies the JOIN condition. One and not more than one. This is because B.id is UNIQUE. Here you are the pseudo-code that illustrates server-side processing:

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

ref . Imagine another C table with columns (id, text), in which id is an index, but not UNIQUE. Table C contains the following data:

 1, John! 1, Jack!

Imagine that as a CONNECTION between A and C the reference is implied:

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

Here you are the pseudo-code that illustrates 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 first one, because for each row checked in table A, there are several possible rows in table C that can satisfy the JOIN condition (without interruption in the above loop), This is because C.ID is NOT UNIQUE .

I hope this helps ...

Cheerz!

+15
source share

"Type" refers to the type of connection made in your request. From best to worst, here is a list:

  • system
  • Const
  • eq_ref
  • out
  • Range
  • Index
  • everything

You will find a more detailed explanation in the MySQL documentation: http://dev.mysql.com/doc/refman/5.0/en/explain-output.html

+3
source share

More articles:


All Articles