mysql explain 竖着显示_虽然INDEX存在，但MySQL EXPLAIN显示所有类型

I ran the below query using EXPLAIN

EXPLAIN SELECT form.id AS `Reference No.`,

department.name AS `Department`,

section.name AS `Section`

FROM form

JOIN department

ON form.deptid = department.id

JOIN section

ON form.sectid = section.id

Does type ALL in the first row indicate that there is going to be performance issues?

+----+-------------+------------+--------+---------------+---------+---------+----------------------+------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows |

+----+-------------+------------+--------+---------------+---------+---------+----------------------+------+

| 1 | SIMPLE | form | ALL | deptid,sectid | | | | 779 |

| 1 | SIMPLE | department | eq_ref | PRIMARY | PRIMARY | 4 | wfs-test.form.deptid | 1 |

| 1 | SIMPLE | section | eq_ref | PRIMARY | PRIMARY | 4 | wfs-test.form.sectid | 1 |

+----+-------------+------------+--------+---------------+---------+---------+----------------------+------+

解决方案

There is no reason for MySQL to use the index when it gets data from table form. Because the query doesn't have any WHERE clause, potentially all the rows from table from will be included in the final result set. More, because there is no ORDER BY clause, any order of the rows is good enough. This is why MySQL gets the rows directly from the table, without consulting any index.

Adding a WHERE condition could trigger the use of an index if an index that contains (some of) the fields involved in the WHERE conditions exist (and the fields, put in the correct order, are the leftmost columns included in the index).

Adding an ORDER BY clause (without WHERE) on fields from table form could also trigger the use of an index when all the fields selected from table form are contained in the index. It will change the type from ALL to index which means it will do a full scan of the index instead of the data rows to get the data it needs. While this is still a full scan, a full index scan usually runs faster than a full table scan because less data is loaded from the storage and parsed (the index is usually smaller than the table data).

More information about this can be found in the MySQL documentation.

The entire section "8.2.1 Optimizing SELECT Statements" is worth reading to better understand how to write faster queries.