mysql> explain select permission.* from t_rbac_permission permission inner JOIN t_rbac_acl acl on acl.PERMISSION_ID=permission.ID
where permission.menu=1 and acl.PRINCIPAL_TYPE=0 order by permission.create_date desc;
+----+-------------+------------+--------+--------------------+---------+---------+-----------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+--------------------+---------+---------+-----------------------------+------+----------------------------------------------+
| 1 | SIMPLE | acl | ALL | FKE43AF088F9936F96 | NULL | NULL | NULL | 94 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | permission | eq_ref | PRIMARY | PRIMARY | 98 | bs_common.acl.PERMISSION_ID | 1 | Using where |
+----+-------------+------------+--------+--------------------+---------+---------+-----------------------------+------+----------------------------------------------+
2 rows in set (0.00 sec)
explain select permission.* from t_rbac_permission permission inner JOIN t_rbac_acl acl
on acl.PERMISSION_ID=permission.ID where permission.menu=1 and acl.PRINCIPAL_TYPE=0 ;
+----+-------------+------------+--------+--------------------+---------+---------+-----------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+--------------------+---------+---------+-----------------------------+------+-------------+
| 1 | SIMPLE | acl | ALL | FKE43AF088F9936F96 | NULL | NULL | NULL | 94 | Using where |
| 1 | SIMPLE | permission | eq_ref | PRIMARY | PRIMARY | 98 | bs_common.acl.PERMISSION_ID | 1 | Using where |
+----+-------------+------------+--------+--------------------+---------+---------+-----------------------------+------+-------------+
？？？？？ why 加上 order by permission.create_date desc 的影响？？？
首先查看相关字段是否加上索引如 order by的字段是否加上索引。加上索引之后单表查询看如何？？
ok 单表查询还是Using filesort. 查询相关资料进行组合索引试试看
ok，现在终结了，那么试试连接查询。各种测试结果如下：
例如：
增加索引后
效果如下
