MySQL ORDER BY primary key which is not in WHERE CLAUSE performs slowly

explain_sqls.sql

/* use condition > */
mysql> EXPLAIN SELECT * FROM test WHERE `test`.`activeday` > 10000 AND `test`.`deleted` = 0 ORDER BY id ASC LIMIT 20\G;
*************************** 1. row ***************************
possible_keys: index_on_test_to_activeday
          key: PRIMARY
      key_len: 4
         rows: 40
     filtered: 5.00
        Extra: Using where
          
mysql> EXPLAIN SELECT * FROM test WHERE `test`.`activeday` > 10000 AND `test`.`deleted` = 0 ORDER BY id DESC LIMIT 20\G;
*************************** 1. row ***************************
possible_keys: index_on_test_to_activeday
          key: PRIMARY
      key_len: 4
         rows: 40
     filtered: 5.00
        Extra: Using where

mysql> EXPLAIN SELECT * FROM test WHERE `test`.`activeday` > 10000 AND `test`.`deleted` = 0 LIMIT 20\G;
*************************** 1. row ***************************
possible_keys: index_on_test_to_activeday
          key: index_on_test_to_activeday
      key_len: 5
         rows: 3617697
     filtered: 10.00
        Extra: Using index condition; Using where; Using MRR
          
mysql> EXPLAIN SELECT * FROM test WHERE `test`.`activeday` > 10000 AND `test`.`deleted` = 0 ORDER BY activeday DESC LIMIT 20\G;
*************************** 1. row ***************************
possible_keys: index_on_test_to_activeday
          key: index_on_test_to_activeday
      key_len: 5
         rows: 3617697
     filtered: 10.00
        Extra: Using index condition; Using where

/* use condition < */
mysql> EXPLAIN SELECT * FROM test WHERE `test`.`activeday` < 10000 AND `test`.`deleted` = 0 ORDER BY id ASC LIMIT 20\G;
*************************** 1. row ***************************
possible_keys: index_on_test_to_activeday
          key: index_on_test_to_activeday
      key_len: 5
         rows: 4999
     filtered: 10.00
        Extra: Using index condition; Using where; Using filesort
          
mysql> EXPLAIN SELECT * FROM test WHERE `test`.`activeday` < 10000 AND `test`.`deleted` = 0 ORDER BY id DESC LIMIT 20\G;
*************************** 1. row ***************************
possible_keys: index_on_test_to_activeday
          key: index_on_test_to_activeday
      key_len: 5
         rows: 4999
     filtered: 10.00
        Extra: Using index condition; Using where; Using filesort
          
mysql> EXPLAIN SELECT * FROM test WHERE `test`.`activeday` < 10000 AND `test`.`deleted` = 0  LIMIT 20\G;
*************************** 1. row ***************************
possible_keys: index_on_test_to_activeday
          key: index_on_test_to_activeday
      key_len: 5
         rows: 4999
     filtered: 10.00
        Extra: Using index condition; Using where
         
mysql> EXPLAIN SELECT * FROM test WHERE `test`.`activeday` < 10000 AND `test`.`deleted` = 0 ORDER BY activeday ASC LIMIT 20\G;
*************************** 1. row ***************************
possible_keys: index_on_test_to_activeday
          key: index_on_test_to_activeday
      key_len: 5
         rows: 4999
     filtered: 10.00
        Extra: Using index condition; Using where

informations.sql

mysql> SELECT count(id) FROM test;
+-----------+
| count(id) |
+-----------+
|   6115543 |
+-----------+
1 row in set (1.05 sec)

/* the activeday median value is 6384458 */
mysql> SELECT * FROM test ORDER BY activeday LIMIT 1 OFFSET 3057771;
+---------+-----------+---------+
| id      | activeday | deleted |
+---------+-----------+---------+
| 3192229 |   6384458 |       0 |
+---------+-----------+---------+

mysql> SHOW FULL COLUMNS FROM test;
+-----------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
| Field     | Type    | Collation | Null | Key | Default | Extra | Privileges                      | Comment |
+-----------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
| id        | int(11) | NULL      | NO   | PRI | NULL    |       | select,insert,update,references |         |
| activeday | int(13) | NULL      | YES  | MUL | NULL    |       | select,insert,update,references |         |
| deleted   | int(1)  | NULL      | YES  |     | NULL    |       | select,insert,update,references |         |
+-----------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
3 rows in set (0.01 sec)

mysql> SHOW INDEX FROM test\G;
*************************** 1. row ***************************
        Table: test
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 7168377
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: test
   Non_unique: 1
     Key_name: index_on_test_to_activeday
 Seq_in_index: 1
  Column_name: activeday
    Collation: A
  Cardinality: 5070250
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

query_records.sql

/* activeday > 10 */
(0.6ms)  SELECT * FROM test WHERE activeday > 10 ORDER BY id ASC LIMIT 20
(0.4ms)  SELECT * FROM test WHERE activeday > 10 ORDER BY id DESC LIMIT 20
(53.4ms)  SELECT * FROM test WHERE activeday > 10 LIMIT 20

/* activeday < 10 */
(0.4ms)  SELECT * FROM test WHERE activeday < 10 ORDER BY id ASC LIMIT 20
(0.3ms)  SELECT * FROM test WHERE activeday < 10 ORDER BY id DESC LIMIT 20
(0.3ms)  SELECT * FROM test WHERE activeday < 10 LIMIT 20

/* activeday > 1000 */
(1.7ms)  SELECT * FROM test WHERE activeday > 1000 ORDER BY id ASC LIMIT 20
(0.6ms)  SELECT * FROM test WHERE activeday > 1000 ORDER BY id DESC LIMIT 20
(35.5ms)  SELECT * FROM test WHERE activeday > 1000 LIMIT 20

/* activeday < 1000 */
(2.7ms)  SELECT * FROM test WHERE activeday < 1000 ORDER BY id ASC LIMIT 20
(2.4ms)  SELECT * FROM test WHERE activeday < 1000 ORDER BY id DESC LIMIT 20
(0.6ms)  SELECT * FROM test WHERE activeday < 1000 LIMIT 20

/* activeday > 100000 */
(17.1ms)  SELECT * FROM test WHERE activeday > 100000 ORDER BY id ASC LIMIT 20
(0.4ms)  SELECT * FROM test WHERE activeday > 100000 ORDER BY id DESC LIMIT 20
(45.1ms)  SELECT * FROM test WHERE activeday > 100000 LIMIT 20

/* activeday < 100000 */
(2.7ms)  SELECT * FROM test WHERE activeday < 100000 ORDER BY id ASC LIMIT 20
(1917.8ms)  SELECT * FROM test WHERE activeday < 100000 ORDER BY id DESC LIMIT 20
(26.9ms)  SELECT * FROM test WHERE activeday < 100000 LIMIT 20

/* activeday > 1000000 */
(100.0ms)  SELECT * FROM test WHERE activeday > 1000000 ORDER BY id ASC LIMIT 20
(0.4ms)  SELECT * FROM test WHERE activeday > 1000000 ORDER BY id DESC LIMIT 20
(54.5ms)  SELECT * FROM test WHERE activeday > 1000000 LIMIT 20

/* activeday < 1000000 */
(0.4ms)  SELECT * FROM test WHERE activeday < 1000000 ORDER BY id ASC LIMIT 20
(1855.0ms)  SELECT * FROM test WHERE activeday < 1000000 ORDER BY id DESC LIMIT 20
(29.8ms)  SELECT * FROM test WHERE activeday < 1000000 LIMIT 20

/* activeday > 10000000 */
(1121.7ms)  SELECT * FROM test WHERE activeday > 10000000 ORDER BY id ASC LIMIT 20
(1.5ms)  SELECT * FROM test WHERE activeday > 10000000 ORDER BY id DESC LIMIT 20
(69.1ms)  SELECT * FROM test WHERE activeday > 10000000 LIMIT 20

/* activeday < 10000000 */
(3.8ms)  SELECT * FROM test WHERE activeday < 10000000 ORDER BY id ASC LIMIT 20
(424.8ms)  SELECT * FROM test WHERE activeday < 10000000 ORDER BY id DESC LIMIT 20
(33.8ms)  SELECT * FROM test WHERE activeday < 10000000 LIMIT 20