MYSQL查询优化（一）

Using filesort

排序的时候无法用到索引

 EXPLAIN SELECT DISTINCT
	hostSn,
	engineer_id,
	COUNT(*) AS count
FROM
	wx_repair_order
GROUP BY
	engineer_id

Using temporary

使用了临时表：A temporary table is created to hold the result. This typically happens if you are using GROUP BY, DISTINCT or ORDER BY.

 EXPLAIN SELECT DISTINCT
	hostSn,
	engineer_id,
	COUNT(*) AS count
FROM
	wx_repair_order
GROUP BY
	engineer_id
ORDER BY NULL

DERIVED

派生表：实际上是一种特殊的subquery，它位于SQL语句中FROM子句里面，可以看做是一个单独的表。

先去重然后统计hostSn出现的次数。

EXPLAIN SELECT
	hostSn,
	engineer_id,
	COUNT(*) AS count
FROM
	(
		SELECT DISTINCT
			hostSn,
			engineer_id
		FROM
			wx_repair_order
	) a
GROUP BY
	engineer_id
ORDER BY
	count DESC

参考:
MySQL show processlist说明

Using index, using temporary, using filesort - how to fix this?