A lot of friends in the tuning with MySQL will certainly use the explain to see the execution of the SELECT statement, here is a brief introduction to the results of the meaning of the two columns.
1 Type column The official saying that this column represents the "type of access", and more commonly, the way MySQL finds the data rows needed. One is to introduce the worst from the most efficient to the best: all this is called a full-table scan, and no index,mysql is used to traverse the entire table from beginning to end to find the data rows needed. Efficiency is the worst. For example, the usertype in this table is not an index, and no index is used in the query, so the results of a full table scan occur.
Index in the index type column, meaning is only limited to scan the whole table in the order of the index in order to scan, only in the index order to scan. It's a bit of a sort of avoidance, because the index is already sorted, and the downside is the overhead of reading the entire table in indexed order. as follows, the order by Id,id in this query is the index of the table, but because there are no indexed columns in the where, it is only the index that scans the entire table. (It is emphasized here that there is no index in the Where condition in your query statement, but only when the order by is indexed and is not limited by the limit, type shows all, that is, there is no limit in this case, or a sweep of the table)
Range this is usually the case when you have a between or ">" symbol in your where statement. This range-Scan index scan is better than a full-table scan because it only needs to start at a certain point in the index, while concluding the other point without scanning all indexes. Ref this is also an indexed access, which returns all rows matching a single value, however, it may find multiple rows that match the criteria, so he should belong to a mix of lookups and scans. Const,system when MySQL can optimize the part of the query and convert it into a constant, it will use this type of access. For example, if you put the primary key of a row in a where condition, then MySQL can convert it to a constant and then query. such as: The UID is the primary key, as a where condition can appear this most efficient query results.
2 Extra column extra The information that appears in the column is generally not very important, but there is still a lot of information we can get from this: Using index: This shows that MySQL uses an overlay index to avoid accessing the table's data rows, which is very efficient! Using where: This indicates that the server will filter after the storage engine receives the row. Some of the conditions in the where will have columns that belong to the index, and when it reads using the index, it is filtered, so there are some where statements that do not appear in the extra column with a using where such a description. Using temporary: This means that MySQL uses a temporary table when sorting query results. Using Filesort: This indicates that MySQL uses an external index to sort the data instead of reading in the order of the indexes in the table. Transfer from http://blog.sina.com.cn/s/blog_75a2f94f0101ddhb.html
The meaning of the type column in MySQL explain and the meaning of the extra column
