mysql 索引优化

问如何优化下面的Mysql SQL语句?

SELECT *
FROM employee
WHERE employee.deptName IN ("departA", "departB", "departC")
	AND tbl.locationID = 3
	AND tbl.level > 5;

思路:

  1. 肯定要建立二级联合索引: index( locationID, deptName, level)
  2. 这里要优化一下 SQL IN 语句。用union all 改写
SELECT *
FROM employee
WHERE employee.deptName = "departA"
	AND tbl.locationID = 3
	AND tbl.level > 5
UNION ALL
SELECT *
FROM employee
WHERE employee.deptName = "departB"
	AND tbl.locationID = 3
	AND tbl.level > 5
UNION ALL
SELECT *
FROM employee
WHERE employee.deptName = "departC"
	AND tbl.locationID = 3
	AND tbl.level > 5;

这样就全利用上了上面的联合索引。