understanding MySQL Explain output

Question

I have a couple of questions regarding MySQL explain.

1. In the first step of the evaluation, it utilizes a REF, for join type. However, upon my research on ref it states the following: All rows with matching index values are read from this table for each combination of rows from the previous tables. What is this Previous table? How can there be a previous table if its the initial step?
2. I created an index on S.E, why does it state Using where? at the Extra column instead of Using Index? And it specifically states it uses the index by looking at the KEY column : SE
3. In terms of the order of operations, does MySQL do the process everything in this order? S.E = 5 using index, R.Rid = S.Rid using the records from the previous step, R.B = 5 using the records from the previous step?

INDEX on S.E

mysql> Create index SE on S(E);
Query OK, 0 rows affected (1.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT COUNT(R.RID) FROM R, S WHERE R.RID=S.RID AND R.B=5 AND S.E=5;
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref            | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+
|  1 | SIMPLE      | S     | ref    | RID,SE        | SE      | 5       | const          |    6 | Using where |
|  1 | SIMPLE      | R     | eq_ref | PRIMARY       | PRIMARY | 4       | project2.S.RID |    1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+

DATASET

• Table R has 100,000 rows and the following fields:

  • RID (primary key): an integer from 1 to 100,000
  • B: a random uniformly distributed integer from 1 to 10
  • C: a random uniformly distributed integer from 1 to 1,000

• Table S has 500,000 rows and the following fields:

  • SID (primary key): an integer from 1 to 500,000
  • RID (foreign key to R): a random uniformly distributed integer from 1 to 100,000
  • D: a random uniformly distributed integer from 1 to 100
  • E: a random uniformly distributed integer from 1 to 100,000

Query

SELECT COUNT(R.RID) FROM R, S WHERE R.RID=S.RID AND R.B=5 AND S.E=5;

Answer 1

This query can be re-written like so also:

 SELECT COUNT(R.RID) FROM R, S WHERE R.RID=S.RID AND R.B=5 AND S.E=5;

 to

 SELECT COUNT(R.RID) 
 FROM R
 INNER JOIN S ON R.RID=S.RID
 WHERE R.B=5 AND S.E=5;

1. What is previous table

The verbiage on http://dev.mysql.com/doc/refman/5.7/en/explain-output.html for ref and eq_ref can be somewhat confusing. There are two tables here. Each one refers to the other as the previous table (this is my interpretation). The join here is inner join, so the database matches all R records with S and considers only those records suitable for review if RID matches.

The confusion also arises when docs refer to the same example for both eq_ref and ref:

SELECT * FROM ref_table,other_table 
WHERE ref_table.key_column=other_table.column;

Overall, when explain mentions ref and eq_ref, I look at the respective tables to see what kind of join they are in. eq_ref looks at PRIMARY or UNIQUE keys. ref is likely to be using an index other than PRIMARY/UNIQUE.

2. Using where

Using where is actually using index as indicated by key column of explain's output. Index is used to satisfy where ... S.E = 5 and is also then used to lookup data in the table.

If the index was covering and data in the table didn't require to be looked up, you could see either using index (if no where condition is used) or using index; using where (if where condition is used)

This information is identical to the link Martin Seitl provided in his comments

3. Order of events

It is my understanding that:

• MySQL is first looking up S.E index for a constant value where ... S.E = 5.
• It then looks up S.RID in table S
• It then matches up records it has so far discovered with R.RID
• The most suitable way to do that is to reach out to the primary key of R, which is RID (therefore R has eq_ref)
• Since R.RID primary key is actually the entire row, R.B = 5 can be satisfied easily. So no other work needs to be done