-- ini benchmark ulang, dengan menghilangkan order by di outer query, dan melakukan select thd nama kolom ketimbang select *
-- data hasil query tidak ditampilkan, karena sama saja dengan data sebelumnya. Fokus hanya pada waktu query.
-- kolom full_name sudah dijadikan index.

-- data sejumlah 1juta row, per halaman 20 data, berarti total ada 50ribu data
-- sekarang coba kita ambil data di halaman 49.999 sebanyak 20 data
-- offset = ( `49999 - 1 ) * 20 = 999960

-- cara biasa, ambil data urut full_name
SELECT `id`,
       `full_name`,
       `birth_date`,
       `email`,
       `register_date`
FROM `users`
ORDER BY `full_name` ASC LIMIT 999960, 20;
-- hasil
20 rows in set (2 min 19.67 sec)
-- explain
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
-- pakai subquery

SET @row_number=0;
SELECT `pos`,
       `id`,
       `full_name`,
       `birth_date`,
       `email`,
       `register_date`
FROM
  (SELECT @row_number:=@row_number+1 AS `pos`, `id`,
                                               `full_name`,
                                               `birth_date`,
                                               `email`,
                                               `register_date`
   FROM `users`
   ORDER BY `full_name` ASC) `users_with_pos`
WHERE `pos` > 999960 LIMIT 20;

-- hasil
20 rows in set (2 min 26.87 sec) [select *]
20 rows in set (1 min 36.70 sec)
-- explain
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |    33.33 | Using where    |
|  2 | DERIVED     | users      | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+

-- desc
SELECT `id`,
       `full_name`,
       `birth_date`,
       `email`,
       `register_date`
FROM `users`
ORDER BY `full_name` DESC LIMIT 999960, 20;

-- hasil
20 rows in set (2 min 37.97 sec)
-- explain
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
-- pakai subquery

SET @row_number=0;
SELECT `pos`,
       `id`,
       `full_name`,
       `birth_date`,
       `email`,
       `register_date`
FROM
  (SELECT @row_number:=@row_number+1 AS `pos`, `id`,
                                               `full_name`,
                                               `birth_date`,
                                               `email`,
                                               `register_date`
   FROM `users`
   ORDER BY `full_name` DESC) `users_with_pos`
WHERE `pos` > 999960 LIMIT 20;

-- hasil
20 rows in set (2 min 41.67 sec) [select *]
20 rows in set (2 min 28.55 sec)
-- explain
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |    33.33 | Using where    |
|  2 | DERIVED     | users      | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+

-- halaman 4, offset 60, limit 20
SELECT `id`,
       `full_name`,
       `birth_date`,
       `email`,
       `register_date`
FROM `users`
ORDER BY `full_name` ASC LIMIT 60, 20;

-- hasil
20 rows in set (0.07 sec)
-- explain
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key             | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | index | NULL          | users_full_name | 258     | NULL |   80 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+
-- pakai subquery

SET @row_number=0;
SELECT `pos`,
       `id`,
       `full_name`,
       `birth_date`,
       `email`,
       `register_date`
FROM
  (SELECT @row_number:=@row_number+1 AS `pos`, `id`,
                                               `full_name`,
                                               `birth_date`,
                                               `email`,
                                               `register_date`
   FROM `users`
   ORDER BY `full_name` ASC) `users_with_pos`
WHERE `pos` > 60 LIMIT 20;

-- hasil
20 rows in set (2 min 35.46 sec) [select *]
20 rows in set (1 min 53.38 sec)
-- explain
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |    33.33 | Using where    |
|  2 | DERIVED     | users      | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+

-- desc
SELECT `id`,
       `full_name`,
       `birth_date`,
       `email`,
       `register_date`
FROM `users`
ORDER BY `full_name` DESC LIMIT 60, 20;

-- hasil
20 rows in set (0.20 sec)
-- explain
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key             | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | index | NULL          | users_full_name | 258     | NULL |   80 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+
-- pakai subquery

SET @row_number=0;
SELECT `pos`,
       `id`,
       `full_name`,
       `birth_date`,
       `email`,
       `register_date`
FROM
  (SELECT @row_number:=@row_number+1 AS `pos`, `id`,
                                               `full_name`,
                                               `birth_date`,
                                               `email`,
                                               `register_date`
   FROM `users`
   ORDER BY `full_name` DESC) `users_with_pos`
WHERE `pos` > 60 LIMIT 20;

-- hasil
20 rows in set (2 min 37.37 sec) [select *]
20 rows in set (1 min 1.63 sec)
-- explain
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |    33.33 | Using where    |
|  2 | DERIVED     | users      | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+