whenever we prefix an sql query using the keyword "explain" we obtain a table with a few posts. Please let me know what's the "type" column. Exactly what does eq_ref and ref mean for the reason that

I'll try a reason...

eq_ref – imagine you have two tables. Table A with posts (id, text) where id is really a primary key. Table B with similar posts (id, text) where id is really a primary key. Table A has got the following data:

1, Hello 
2, How are

Table B has got the following data:

1, world!
2, you?

Imagine eq_ref as JOIN from a and B:

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

This JOIN becomes manifest pretty quickly because for every row scanned in table A there might be only ONE row in table B which satisfies the JOIN condition. One and a maximum of one. That's because B.id is exclusive. Here you're a pseudo code which demonstrates the processing at server side:

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

ref - Imagine another table C with posts (id, text) by which id a catalog but a non UNIQUE one. Table C has got the following data:

1, John!
1, Jack!

Imagine ref as JOIN from a and C:

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

Here you're a pseudo code which demonstrates the processing at server side:

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

}

This JOIN isn't as fast because the former one because for every row scanned in table A you will find SEVERAL possible rows in table C which might fulfill the JOIN condition (no break informed above). That's because C.ID isn't UNIQUE.

Hopefully helps...

Cheerz!

The "type" refers back to the join type produced in your request. From better to worst, this is actually the list :

  • system
  • const
  • eq_ref
  • ref
  • range
  • index
  • all

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