mysql 索引 limit优化

Question

这是创建的索引：

 mysql> show index from salaries;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| salaries |          0 | PRIMARY  |            1 | emp_no      | A         |      295694 |     NULL | NULL   |      | BTREE      |         |               |
| salaries |          0 | PRIMARY  |            2 | from_date   | A         |     2838426 |     NULL | NULL   |      | BTREE      |         |               |
| salaries |          1 | salary   |            1 | salary      | A         |       73927 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

这是按照网上的sql优化和没有优化的结果，为何优化的效率比没有优化还要低？

 mysql> select * from salaries a join (select emp_no,from_date from salaries order by from_date limit 2000000,20) b on a.emp_no=b.emp_no and a.from_date=b.from_date;
+--------+--------+------------+------------+--------+------------+
| emp_no | salary | from_date  | to_date    | emp_no | from_date  |
+--------+--------+------------+------------+--------+------------+
|  49095 |  52441 | 1999-03-29 | 2000-03-28 |  49095 | 1999-03-29 |
|  49534 | 106829 | 1999-03-29 | 2000-03-28 |  49534 | 1999-03-29 |
|  49661 |  88589 | 1999-03-29 | 2000-03-28 |  49661 | 1999-03-29 |
|  47516 |  89305 | 1999-03-29 | 2000-03-28 |  47516 | 1999-03-29 |
|  47520 |  48996 | 1999-03-29 | 2000-03-28 |  47520 | 1999-03-29 |
|  48129 |  65100 | 1999-03-29 | 2000-03-28 |  48129 | 1999-03-29 |
|  42510 |  43998 | 1999-03-29 | 2000-03-28 |  42510 | 1999-03-29 |
|  43186 |  52203 | 1999-03-29 | 2000-03-28 |  43186 | 1999-03-29 |
|  51434 |  58226 | 1999-03-29 | 2000-03-28 |  51434 | 1999-03-29 |
|  45635 |  53164 | 1999-03-29 | 2000-03-28 |  45635 | 1999-03-29 |
|  45823 |  65169 | 1999-03-29 | 2000-03-28 |  45823 | 1999-03-29 |
|  45866 |  43137 | 1999-03-29 | 1999-05-16 |  45866 | 1999-03-29 |
|  46780 |  55729 | 1999-03-29 | 2000-03-28 |  46780 | 1999-03-29 |
|  68262 |  57090 | 1999-03-29 | 2000-03-28 |  68262 | 1999-03-29 |
|  69335 |  77710 | 1999-03-29 | 2000-03-28 |  69335 | 1999-03-29 |
|  69573 |  52088 | 1999-03-29 | 2000-03-28 |  69573 | 1999-03-29 |
|  67465 |  81145 | 1999-03-29 | 2000-03-28 |  67465 | 1999-03-29 |
|  67472 |  48556 | 1999-03-29 | 2000-03-28 |  67472 | 1999-03-29 |
|  67475 |  79215 | 1999-03-29 | 2000-03-28 |  67475 | 1999-03-29 |
|  71081 |  60817 | 1999-03-29 | 2000-03-28 |  71081 | 1999-03-29 |
+--------+--------+------------+------------+--------+------------+
20 rows in set (1.39 sec)

mysql> select * from salaries limit 2000000,20;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
| 410897 |  50288 | 1992-09-06 | 1993-09-06 |
| 410897 |  51656 | 1993-09-06 | 1994-09-06 |
| 410897 |  53408 | 1994-09-06 | 1995-09-06 |
| 410897 |  55183 | 1995-09-06 | 1996-09-05 |
| 410897 |  55166 | 1996-09-05 | 1997-09-05 |
| 410897 |  59264 | 1997-09-05 | 1998-09-05 |
| 410897 |  62534 | 1998-09-05 | 1999-09-05 |
| 410897 |  62825 | 1999-09-05 | 2000-09-04 |
| 410897 |  66931 | 2000-09-04 | 2001-09-04 |
| 410897 |  68054 | 2001-09-04 | 9999-01-01 |
| 410898 |  66156 | 1990-10-25 | 1991-10-25 |
| 410898 |  69219 | 1991-10-25 | 1992-10-24 |
| 410898 |  70371 | 1992-10-24 | 1993-10-24 |
| 410898 |  70666 | 1993-10-24 | 1994-10-24 |
| 410898 |  73620 | 1994-10-24 | 1995-10-24 |
| 410898 |  75197 | 1995-10-24 | 1996-10-23 |
| 410898 |  78083 | 1996-10-23 | 1997-10-23 |
| 410898 |  81043 | 1997-10-23 | 1998-10-23 |
| 410898 |  81128 | 1998-10-23 | 1999-10-23 |
| 410898 |  84051 | 1999-10-23 | 2000-10-22 |
+--------+--------+------------+------------+
20 rows in set (0.42 sec)

Answer 1

前一个有子查询、连接操作，还排序，当然比后一个慢了。有什么问题。