-- struktur tabel:
-- DROP TABLE IF EXISTS `users`;
-- CREATE TABLE `users` (
-- `id` mediumint(8) unsigned NOT NULL auto_increment,
-- `full_name` varchar(255) default NULL,
-- `birth_date` varchar(255),
-- `email` varchar(255) default NULL,
-- `register_date` varchar(255),
-- PRIMARY KEY (`id`)
-- ) AUTO_INCREMENT=1;
-- total data
SELECT COUNT(*)
FROM `users`;
-- hasil
+----------+
| COUNT(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.16 sec)
-- 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
-- tanpa index
-- 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
+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
| id | full_name | birth_date | email | register_date |
+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
| 711969 | Zorita Walls | 1989-11-29 07:11:47 | [email protected] | 2015-07-31 07:42:27 |
| 689376 | Zorita Walsh | 1990-10-17 22:55:56 | [email protected] | 2015-08-29 07:58:20 |
| 785577 | Zorita Walter | 1985-11-16 05:52:37 | [email protected] | 2016-02-22 07:00:58 |
| 992994 | Zorita Walton | 1992-04-04 21:13:24 | In@sit.com | 2015-10-09 09:39:23 |
| 139823 | Zorita Ware | 1993-06-25 05:16:48 | [email protected] | 2015-07-27 03:34:34 |
| 373327 | Zorita Ware | 1987-11-04 17:32:51 | [email protected] | 2015-09-19 17:27:44 |
| 789023 | Zorita Washington | 1998-10-14 01:55:58 | [email protected] | 2015-05-03 14:48:36 |
| 112559 | Zorita Washington | 1980-07-16 17:08:44 | [email protected] | 2015-02-25 23:11:41 |
| 681705 | Zorita Webster | 1981-08-05 12:07:14 | [email protected] | 2015-05-30 15:02:23 |
| 395543 | Zorita Weeks | 1982-08-20 04:23:21 | [email protected] | 2015-12-13 07:40:03 |
| 993066 | Zorita Weeks | 2000-05-03 15:43:28 | [email protected] | 2015-05-05 00:40:31 |
| 181338 | Zorita Welch | 1995-01-21 09:01:06 | purus.in[email protected] | 2015-09-24 22:26:52 |
| 362332 | Zorita Welch | 2000-12-19 22:52:45 | [email protected] | 2015-10-15 11:35:38 |
| 167832 | Zorita Welch | 1994-03-03 05:15:59 | [email protected] | 2015-10-21 13:19:32 |
| 501730 | Zorita Welch | 1998-05-28 19:29:57 | [email protected] | 2015-08-04 14:03:28 |
| 437806 | Zorita West | 1991-02-12 15:29:52 | [email protected] | 2015-05-07 00:46:19 |
| 519870 | Zorita Whitaker | 1987-07-28 23:19:42 | [email protected] | 2015-11-19 03:56:00 |
| 990047 | Zorita Whitehead | 1992-08-26 20:44:48 | [email protected] | 2015-05-18 23:34:23 |
| 213451 | Zorita Whitehead | 2000-04-25 20:40:43 | [email protected] | 2015-08-12 06:08:08 |
| 167061 | Zorita Whitney | 1984-09-10 13:42:37 | [email protected] | 2015-10-03 15:50:01 |
+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
20 rows in set (1 min 49.39 sec)
-- pakai posisi
SET @row_number=0;
SELECT *
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
ORDER BY `pos` ASC
LIMIT 20;
-- hasil
+--------+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
| pos | id | full_name | birth_date | email | register_date |
+--------+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
| 999961 | 711969 | Zorita Walls | 1989-11-29 07:11:47 | [email protected] | 2015-07-31 07:42:27 |
| 999962 | 689376 | Zorita Walsh | 1990-10-17 22:55:56 | [email protected] | 2015-08-29 07:58:20 |
| 999963 | 785577 | Zorita Walter | 1985-11-16 05:52:37 | [email protected] | 2016-02-22 07:00:58 |
| 999964 | 992994 | Zorita Walton | 1992-04-04 21:13:24 | In@sit.com | 2015-10-09 09:39:23 |
| 999965 | 139823 | Zorita Ware | 1993-06-25 05:16:48 | [email protected] | 2015-07-27 03:34:34 |
| 999966 | 373327 | Zorita Ware | 1987-11-04 17:32:51 | [email protected] | 2015-09-19 17:27:44 |
| 999967 | 789023 | Zorita Washington | 1998-10-14 01:55:58 | [email protected] | 2015-05-03 14:48:36 |
| 999968 | 112559 | Zorita Washington | 1980-07-16 17:08:44 | [email protected] | 2015-02-25 23:11:41 |
| 999969 | 681705 | Zorita Webster | 1981-08-05 12:07:14 | [email protected] | 2015-05-30 15:02:23 |
| 999970 | 395543 | Zorita Weeks | 1982-08-20 04:23:21 | [email protected] | 2015-12-13 07:40:03 |
| 999971 | 993066 | Zorita Weeks | 2000-05-03 15:43:28 | [email protected] | 2015-05-05 00:40:31 |
| 999972 | 181338 | Zorita Welch | 1995-01-21 09:01:06 | purus.in[email protected] | 2015-09-24 22:26:52 |
| 999973 | 362332 | Zorita Welch | 2000-12-19 22:52:45 | [email protected] | 2015-10-15 11:35:38 |
| 999974 | 167832 | Zorita Welch | 1994-03-03 05:15:59 | [email protected] | 2015-10-21 13:19:32 |
| 999975 | 501730 | Zorita Welch | 1998-05-28 19:29:57 | [email protected] | 2015-08-04 14:03:28 |
| 999976 | 437806 | Zorita West | 1991-02-12 15:29:52 | [email protected] | 2015-05-07 00:46:19 |
| 999977 | 519870 | Zorita Whitaker | 1987-07-28 23:19:42 | [email protected] | 2015-11-19 03:56:00 |
| 999978 | 990047 | Zorita Whitehead | 1992-08-26 20:44:48 | [email protected] | 2015-05-18 23:34:23 |
| 999979 | 213451 | Zorita Whitehead | 2000-04-25 20:40:43 | [email protected] | 2015-08-12 06:08:08 |
| 999980 | 167061 | Zorita Whitney | 1984-09-10 13:42:37 | [email protected] | 2015-10-03 15:50:01 |
+--------+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
20 rows in set (1 min 31.27 sec)
-- catatan: bedanya hanya 18.12 sec. Memang pakai posisi jadi sedikit lebih cepat
-- desc
SELECT `id`, `full_name`, `birth_date`, `email`, `register_date`
FROM `users`
ORDER BY `full_name` DESC
LIMIT 999960, 20;
-- hasil
+--------+----------------+---------------------+------------------------------------------------+---------------------+
| id | full_name | birth_date | email | register_date |
+--------+----------------+---------------------+------------------------------------------------+---------------------+
| 319574 | Aaron Berg | 1988-05-31 15:27:22 | [email protected] | 2015-01-23 15:21:05 |
| 734226 | Aaron Benton | 1981-06-05 16:33:39 | [email protected] | 2015-10-29 22:01:27 |
| 673274 | Aaron Benson | 1995-07-22 04:58:13 | [email protected] | 2016-01-01 14:50:53 |
| 134255 | Aaron Bennett | 1989-08-22 13:31:34 | [email protected] | 2015-09-15 13:28:26 |
| 841412 | Aaron Bennett | 1983-04-12 10:56:38 | [email protected] | 2015-07-09 01:56:28 |
| 64031 | Aaron Benjamin | 1981-04-20 16:24:33 | [email protected] | 2015-06-13 02:07:37 |
| 181292 | Aaron Benjamin | 1982-11-15 06:59:12 | [email protected] | 2015-10-26 21:10:27 |
| 840189 | Aaron Bender | 1997-09-22 01:39:04 | [email protected] | 2015-10-05 22:42:04 |
| 370860 | Aaron Becker | 1986-04-02 05:48:27 | [email protected] | 2015-01-27 03:12:10 |
| 229737 | Aaron Beasley | 1986-10-16 07:44:46 | [email protected] | 2015-03-01 06:38:21 |
| 789904 | Aaron Beasley | 1984-05-12 16:57:10 | [email protected] | 2016-02-24 00:15:19 |
| 773388 | Aaron Beard | 1986-03-25 03:32:17 | [email protected] | 2015-12-07 12:53:11 |
| 171623 | Aaron Beard | 1992-07-29 18:52:29 | [email protected] | 2015-06-15 01:45:45 |
| 658446 | Aaron Bean | 1989-03-13 15:54:56 | [email protected] | 2015-06-07 07:52:42 |
| 407688 | Aaron Baxter | 1983-03-26 19:19:01 | [email protected] | 2015-09-03 20:55:30 |
| 924833 | Aaron Bauer | 1999-12-11 13:28:22 | [email protected] | 2016-01-19 00:30:24 |
| 777753 | Aaron Battle | 1997-11-24 21:24:09 | [email protected] | 2015-05-08 00:17:21 |
| 217447 | Aaron Bass | 1992-12-27 15:59:17 | [email protected] | 2015-01-06 05:07:39 |
| 732683 | Aaron Bartlett | 1984-03-07 10:22:55 | [email protected] | 2016-02-08 13:03:43 |
| 158931 | Aaron Bartlett | 1997-07-23 00:43:30 | libero.Integer.in@risusDonec.ca | 2016-01-12 04:29:50 |
+--------+----------------+---------------------+------------------------------------------------+---------------------+
20 rows in set (2 min 17.52 sec)
SET @row_number=0;
SELECT *
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
ORDER BY `pos` ASC
LIMIT 20;
-- hasil
+--------+--------+----------------+---------------------+------------------------------------------------+---------------------+
| pos | id | full_name | birth_date | email | register_date |
+--------+--------+----------------+---------------------+------------------------------------------------+---------------------+
| 999961 | 319574 | Aaron Berg | 1988-05-31 15:27:22 | [email protected] | 2015-01-23 15:21:05 |
| 999962 | 734226 | Aaron Benton | 1981-06-05 16:33:39 | [email protected] | 2015-10-29 22:01:27 |
| 999963 | 673274 | Aaron Benson | 1995-07-22 04:58:13 | [email protected] | 2016-01-01 14:50:53 |
| 999964 | 134255 | Aaron Bennett | 1989-08-22 13:31:34 | [email protected] | 2015-09-15 13:28:26 |
| 999965 | 841412 | Aaron Bennett | 1983-04-12 10:56:38 | [email protected] | 2015-07-09 01:56:28 |
| 999966 | 64031 | Aaron Benjamin | 1981-04-20 16:24:33 | [email protected] | 2015-06-13 02:07:37 |
| 999967 | 181292 | Aaron Benjamin | 1982-11-15 06:59:12 | [email protected] | 2015-10-26 21:10:27 |
| 999968 | 840189 | Aaron Bender | 1997-09-22 01:39:04 | [email protected] | 2015-10-05 22:42:04 |
| 999969 | 370860 | Aaron Becker | 1986-04-02 05:48:27 | [email protected] | 2015-01-27 03:12:10 |
| 999970 | 229737 | Aaron Beasley | 1986-10-16 07:44:46 | [email protected] | 2015-03-01 06:38:21 |
| 999971 | 789904 | Aaron Beasley | 1984-05-12 16:57:10 | [email protected] | 2016-02-24 00:15:19 |
| 999972 | 773388 | Aaron Beard | 1986-03-25 03:32:17 | [email protected] | 2015-12-07 12:53:11 |
| 999973 | 171623 | Aaron Beard | 1992-07-29 18:52:29 | [email protected] | 2015-06-15 01:45:45 |
| 999974 | 658446 | Aaron Bean | 1989-03-13 15:54:56 | [email protected] | 2015-06-07 07:52:42 |
| 999975 | 407688 | Aaron Baxter | 1983-03-26 19:19:01 | [email protected] | 2015-09-03 20:55:30 |
| 999976 | 924833 | Aaron Bauer | 1999-12-11 13:28:22 | [email protected] | 2016-01-19 00:30:24 |
| 999977 | 777753 | Aaron Battle | 1997-11-24 21:24:09 | [email protected] | 2015-05-08 00:17:21 |
| 999978 | 217447 | Aaron Bass | 1992-12-27 15:59:17 | [email protected] | 2015-01-06 05:07:39 |
| 999979 | 732683 | Aaron Bartlett | 1984-03-07 10:22:55 | [email protected] | 2016-02-08 13:03:43 |
| 999980 | 158931 | Aaron Bartlett | 1997-07-23 00:43:30 | libero.Integer.in@risusDonec.ca | 2016-01-12 04:29:50 |
+--------+--------+----------------+---------------------+------------------------------------------------+---------------------+
20 rows in set (1 min 14.02 sec)
-- catatan: kecepatannya hampir 2x cara biasa
-- sekarang mari tambahkan index di kolom full_name
CREATE INDEX `users_full_name` ON `users` (`full_name`);
mysql> CREATE INDEX `users_full_name` ON `users` (`full_name`);
Query OK, 0 rows affected (5.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- cara biasa:
+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
| id | full_name | birth_date | email | register_date |
+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
| 711969 | Zorita Walls | 1989-11-29 07:11:47 | [email protected] | 2015-07-31 07:42:27 |
| 689376 | Zorita Walsh | 1990-10-17 22:55:56 | [email protected] | 2015-08-29 07:58:20 |
| 785577 | Zorita Walter | 1985-11-16 05:52:37 | [email protected] | 2016-02-22 07:00:58 |
| 992994 | Zorita Walton | 1992-04-04 21:13:24 | In@sit.com | 2015-10-09 09:39:23 |
| 139823 | Zorita Ware | 1993-06-25 05:16:48 | [email protected] | 2015-07-27 03:34:34 |
| 373327 | Zorita Ware | 1987-11-04 17:32:51 | [email protected] | 2015-09-19 17:27:44 |
| 789023 | Zorita Washington | 1998-10-14 01:55:58 | [email protected] | 2015-05-03 14:48:36 |
| 112559 | Zorita Washington | 1980-07-16 17:08:44 | [email protected] | 2015-02-25 23:11:41 |
| 681705 | Zorita Webster | 1981-08-05 12:07:14 | [email protected] | 2015-05-30 15:02:23 |
| 395543 | Zorita Weeks | 1982-08-20 04:23:21 | [email protected] | 2015-12-13 07:40:03 |
| 993066 | Zorita Weeks | 2000-05-03 15:43:28 | [email protected] | 2015-05-05 00:40:31 |
| 181338 | Zorita Welch | 1995-01-21 09:01:06 | purus.in[email protected] | 2015-09-24 22:26:52 |
| 362332 | Zorita Welch | 2000-12-19 22:52:45 | [email protected] | 2015-10-15 11:35:38 |
| 167832 | Zorita Welch | 1994-03-03 05:15:59 | [email protected] | 2015-10-21 13:19:32 |
| 501730 | Zorita Welch | 1998-05-28 19:29:57 | [email protected] | 2015-08-04 14:03:28 |
| 437806 | Zorita West | 1991-02-12 15:29:52 | [email protected] | 2015-05-07 00:46:19 |
| 519870 | Zorita Whitaker | 1987-07-28 23:19:42 | [email protected] | 2015-11-19 03:56:00 |
| 990047 | Zorita Whitehead | 1992-08-26 20:44:48 | [email protected] | 2015-05-18 23:34:23 |
| 213451 | Zorita Whitehead | 2000-04-25 20:40:43 | [email protected] | 2015-08-12 06:08:08 |
| 167061 | Zorita Whitney | 1984-09-10 13:42:37 | [email protected] | 2015-10-03 15:50:01 |
+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
20 rows in set (1 min 15.27 sec)
-- hasil 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
-- cara pos:
+--------+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
| pos | id | full_name | birth_date | email | register_date |
+--------+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
| 999961 | 711969 | Zorita Walls | 1989-11-29 07:11:47 | [email protected] | 2015-07-31 07:42:27 |
| 999962 | 689376 | Zorita Walsh | 1990-10-17 22:55:56 | [email protected] | 2015-08-29 07:58:20 |
| 999963 | 785577 | Zorita Walter | 1985-11-16 05:52:37 | [email protected] | 2016-02-22 07:00:58 |
| 999964 | 992994 | Zorita Walton | 1992-04-04 21:13:24 | In@sit.com | 2015-10-09 09:39:23 |
| 999965 | 139823 | Zorita Ware | 1993-06-25 05:16:48 | [email protected] | 2015-07-27 03:34:34 |
| 999966 | 373327 | Zorita Ware | 1987-11-04 17:32:51 | [email protected] | 2015-09-19 17:27:44 |
| 999967 | 789023 | Zorita Washington | 1998-10-14 01:55:58 | [email protected] | 2015-05-03 14:48:36 |
| 999968 | 112559 | Zorita Washington | 1980-07-16 17:08:44 | [email protected] | 2015-02-25 23:11:41 |
| 999969 | 681705 | Zorita Webster | 1981-08-05 12:07:14 | [email protected] | 2015-05-30 15:02:23 |
| 999970 | 395543 | Zorita Weeks | 1982-08-20 04:23:21 | [email protected] | 2015-12-13 07:40:03 |
| 999971 | 993066 | Zorita Weeks | 2000-05-03 15:43:28 | [email protected] | 2015-05-05 00:40:31 |
| 999972 | 181338 | Zorita Welch | 1995-01-21 09:01:06 | purus.in[email protected] | 2015-09-24 22:26:52 |
| 999973 | 362332 | Zorita Welch | 2000-12-19 22:52:45 | [email protected] | 2015-10-15 11:35:38 |
| 999974 | 167832 | Zorita Welch | 1994-03-03 05:15:59 | [email protected] | 2015-10-21 13:19:32 |
| 999975 | 501730 | Zorita Welch | 1998-05-28 19:29:57 | [email protected] | 2015-08-04 14:03:28 |
| 999976 | 437806 | Zorita West | 1991-02-12 15:29:52 | [email protected] | 2015-05-07 00:46:19 |
| 999977 | 519870 | Zorita Whitaker | 1987-07-28 23:19:42 | [email protected] | 2015-11-19 03:56:00 |
| 999978 | 990047 | Zorita Whitehead | 1992-08-26 20:44:48 | [email protected] | 2015-05-18 23:34:23 |
| 999979 | 213451 | Zorita Whitehead | 2000-04-25 20:40:43 | [email protected] | 2015-08-12 06:08:08 |
| 999980 | 167061 | Zorita Whitney | 1984-09-10 13:42:37 | [email protected] | 2015-10-03 15:50:01 |
+--------+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
20 rows in set (53.28 sec)
-- hasil 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; Using filesort |
| 2 | DERIVED | users | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
-- catatan: wow, lebih dari 2 kali lipatnya
-- desc
-- cara biasa
+--------+----------------+---------------------+------------------------------------------------+---------------------+
| id | full_name | birth_date | email | register_date |
+--------+----------------+---------------------+------------------------------------------------+---------------------+
| 319574 | Aaron Berg | 1988-05-31 15:27:22 | [email protected] | 2015-01-23 15:21:05 |
| 734226 | Aaron Benton | 1981-06-05 16:33:39 | [email protected] | 2015-10-29 22:01:27 |
| 673274 | Aaron Benson | 1995-07-22 04:58:13 | [email protected] | 2016-01-01 14:50:53 |
| 134255 | Aaron Bennett | 1989-08-22 13:31:34 | [email protected] | 2015-09-15 13:28:26 |
| 841412 | Aaron Bennett | 1983-04-12 10:56:38 | [email protected] | 2015-07-09 01:56:28 |
| 64031 | Aaron Benjamin | 1981-04-20 16:24:33 | [email protected] | 2015-06-13 02:07:37 |
| 181292 | Aaron Benjamin | 1982-11-15 06:59:12 | [email protected] | 2015-10-26 21:10:27 |
| 840189 | Aaron Bender | 1997-09-22 01:39:04 | [email protected] | 2015-10-05 22:42:04 |
| 370860 | Aaron Becker | 1986-04-02 05:48:27 | [email protected] | 2015-01-27 03:12:10 |
| 229737 | Aaron Beasley | 1986-10-16 07:44:46 | [email protected] | 2015-03-01 06:38:21 |
| 789904 | Aaron Beasley | 1984-05-12 16:57:10 | [email protected] | 2016-02-24 00:15:19 |
| 773388 | Aaron Beard | 1986-03-25 03:32:17 | [email protected] | 2015-12-07 12:53:11 |
| 171623 | Aaron Beard | 1992-07-29 18:52:29 | [email protected] | 2015-06-15 01:45:45 |
| 658446 | Aaron Bean | 1989-03-13 15:54:56 | [email protected] | 2015-06-07 07:52:42 |
| 407688 | Aaron Baxter | 1983-03-26 19:19:01 | [email protected] | 2015-09-03 20:55:30 |
| 924833 | Aaron Bauer | 1999-12-11 13:28:22 | [email protected] | 2016-01-19 00:30:24 |
| 777753 | Aaron Battle | 1997-11-24 21:24:09 | [email protected] | 2015-05-08 00:17:21 |
| 217447 | Aaron Bass | 1992-12-27 15:59:17 | [email protected] | 2015-01-06 05:07:39 |
| 732683 | Aaron Bartlett | 1984-03-07 10:22:55 | [email protected] | 2016-02-08 13:03:43 |
| 158931 | Aaron Bartlett | 1997-07-23 00:43:30 | libero.Integer.in@risusDonec.ca | 2016-01-12 04:29:50 |
+--------+----------------+---------------------+------------------------------------------------+---------------------+
20 rows in set (2 min 20.57 sec)
-- hasil 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
-- cara pos
+--------+--------+----------------+---------------------+------------------------------------------------+---------------------+
| pos | id | full_name | birth_date | email | register_date |
+--------+--------+----------------+---------------------+------------------------------------------------+---------------------+
| 999961 | 319574 | Aaron Berg | 1988-05-31 15:27:22 | [email protected] | 2015-01-23 15:21:05 |
| 999962 | 734226 | Aaron Benton | 1981-06-05 16:33:39 | [email protected] | 2015-10-29 22:01:27 |
| 999963 | 673274 | Aaron Benson | 1995-07-22 04:58:13 | [email protected] | 2016-01-01 14:50:53 |
| 999964 | 134255 | Aaron Bennett | 1989-08-22 13:31:34 | [email protected] | 2015-09-15 13:28:26 |
| 999965 | 841412 | Aaron Bennett | 1983-04-12 10:56:38 | [email protected] | 2015-07-09 01:56:28 |
| 999966 | 64031 | Aaron Benjamin | 1981-04-20 16:24:33 | [email protected] | 2015-06-13 02:07:37 |
| 999967 | 181292 | Aaron Benjamin | 1982-11-15 06:59:12 | [email protected] | 2015-10-26 21:10:27 |
| 999968 | 840189 | Aaron Bender | 1997-09-22 01:39:04 | [email protected] | 2015-10-05 22:42:04 |
| 999969 | 370860 | Aaron Becker | 1986-04-02 05:48:27 | [email protected] | 2015-01-27 03:12:10 |
| 999970 | 229737 | Aaron Beasley | 1986-10-16 07:44:46 | [email protected] | 2015-03-01 06:38:21 |
| 999971 | 789904 | Aaron Beasley | 1984-05-12 16:57:10 | [email protected] | 2016-02-24 00:15:19 |
| 999972 | 773388 | Aaron Beard | 1986-03-25 03:32:17 | [email protected] | 2015-12-07 12:53:11 |
| 999973 | 171623 | Aaron Beard | 1992-07-29 18:52:29 | [email protected] | 2015-06-15 01:45:45 |
| 999974 | 658446 | Aaron Bean | 1989-03-13 15:54:56 | [email protected] | 2015-06-07 07:52:42 |
| 999975 | 407688 | Aaron Baxter | 1983-03-26 19:19:01 | [email protected] | 2015-09-03 20:55:30 |
| 999976 | 924833 | Aaron Bauer | 1999-12-11 13:28:22 | [email protected] | 2016-01-19 00:30:24 |
| 999977 | 777753 | Aaron Battle | 1997-11-24 21:24:09 | [email protected] | 2015-05-08 00:17:21 |
| 999978 | 217447 | Aaron Bass | 1992-12-27 15:59:17 | [email protected] | 2015-01-06 05:07:39 |
| 999979 | 732683 | Aaron Bartlett | 1984-03-07 10:22:55 | [email protected] | 2016-02-08 13:03:43 |
| 999980 | 158931 | Aaron Bartlett | 1997-07-23 00:43:30 | libero.Integer.in@risusDonec.ca | 2016-01-12 04:29:50 |
+--------+--------+----------------+---------------------+------------------------------------------------+---------------------+
20 rows in set (54.16 sec)
-- hasil 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; Using filesort |
| 2 | DERIVED | users | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
-- Untuk posisi halaman ada di akhir, memang cara biasa terlihat lebih lambat, tetapi ada anomali yang saya temui jika halaman yang ingin dilihat adalah di awal
-- Cara biasa
SELECT `id`, `full_name`, `birth_date`, `email`, `register_date` FROM `users` ORDER BY `full_name` ASC LIMIT 60, 20;
-- hasil
+--------+----------------+---------------------+---------------------------------------------+---------------------+
| id | full_name | birth_date | email | register_date |
+--------+----------------+---------------------+---------------------------------------------+---------------------+
| 349431 | Aaron Branch | 1999-09-02 07:22:31 | [email protected] | 2015-04-24 00:57:26 |
| 939018 | Aaron Bray | 1991-07-11 22:53:15 | [email protected] | 2015-03-14 00:51:30 |
| 32163 | Aaron Brennan | 1981-07-11 20:24:29 | [email protected] | 2015-11-08 03:25:29 |
| 502845 | Aaron Brennan | 1980-04-08 07:50:48 | [email protected] | 2015-08-13 18:44:07 |
| 556419 | Aaron Brennan | 1997-07-04 10:16:25 | [email protected] | 2015-11-29 20:20:01 |
| 938914 | Aaron Brennan | 1982-01-29 12:45:45 | [email protected] | 2015-07-14 21:53:57 |
| 163125 | Aaron Brewer | 1996-10-22 18:12:20 | [email protected] | 2015-01-27 09:39:47 |
| 819358 | Aaron Brewer | 1995-05-24 12:49:46 | [email protected] | 2015-10-26 02:44:15 |
| 220648 | Aaron Britt | 1985-02-23 13:00:06 | [email protected] | 2015-08-20 02:56:57 |
| 872558 | Aaron Brock | 1984-09-30 04:49:56 | [email protected] | 2015-04-14 11:47:32 |
| 447693 | Aaron Brown | 1985-08-28 02:04:12 | [email protected] | 2015-06-19 05:35:39 |
| 79683 | Aaron Browning | 1980-11-12 15:59:30 | [email protected] | 2015-01-16 23:42:54 |
| 347786 | Aaron Browning | 2000-09-24 05:10:37 | [email protected] | 2016-02-08 14:32:22 |
| 417605 | Aaron Browning | 1995-12-22 08:48:03 | [email protected] | 2015-09-01 16:41:20 |
| 883563 | Aaron Browning | 1998-09-21 17:14:16 | [email protected] | 2015-08-02 11:51:52 |
| 511872 | Aaron Bruce | 1999-07-10 02:32:11 | In[email protected] | 2015-06-13 02:17:22 |
| 526648 | Aaron Buckley | 1982-09-10 19:13:35 | [email protected] | 2016-02-26 16:18:38 |
| 382172 | Aaron Bullock | 1987-08-26 14:36:14 | [email protected] | 2015-12-22 15:49:50 |
| 799606 | Aaron Bullock | 1995-08-01 07:57:22 | [email protected] | 2016-02-22 00:14:35 |
| 690515 | Aaron Burch | 2000-09-03 20:28:23 | [email protected] | 2015-04-05 23:08:28 |
+--------+----------------+---------------------+---------------------------------------------+---------------------+
20 rows in set (0.03 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 |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+
-- cara pos
SET @row_number=0; SELECT * 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 ORDER BY `pos` ASC LIMIT 20;
-- hasil
+------+--------+----------------+---------------------+---------------------------------------------+---------------------+
| pos | id | full_name | birth_date | email | register_date |
+------+--------+----------------+---------------------+---------------------------------------------+---------------------+
| 61 | 349431 | Aaron Branch | 1999-09-02 07:22:31 | [email protected] | 2015-04-24 00:57:26 |
| 62 | 939018 | Aaron Bray | 1991-07-11 22:53:15 | [email protected] | 2015-03-14 00:51:30 |
| 63 | 502845 | Aaron Brennan | 1980-04-08 07:50:48 | [email protected] | 2015-08-13 18:44:07 |
| 64 | 556419 | Aaron Brennan | 1997-07-04 10:16:25 | [email protected] | 2015-11-29 20:20:01 |
| 65 | 938914 | Aaron Brennan | 1982-01-29 12:45:45 | [email protected] | 2015-07-14 21:53:57 |
| 66 | 32163 | Aaron Brennan | 1981-07-11 20:24:29 | [email protected] | 2015-11-08 03:25:29 |
| 67 | 163125 | Aaron Brewer | 1996-10-22 18:12:20 | [email protected] | 2015-01-27 09:39:47 |
| 68 | 819358 | Aaron Brewer | 1995-05-24 12:49:46 | [email protected] | 2015-10-26 02:44:15 |
| 69 | 220648 | Aaron Britt | 1985-02-23 13:00:06 | [email protected] | 2015-08-20 02:56:57 |
| 70 | 872558 | Aaron Brock | 1984-09-30 04:49:56 | [email protected] | 2015-04-14 11:47:32 |
| 71 | 447693 | Aaron Brown | 1985-08-28 02:04:12 | [email protected] | 2015-06-19 05:35:39 |
| 72 | 79683 | Aaron Browning | 1980-11-12 15:59:30 | [email protected] | 2015-01-16 23:42:54 |
| 73 | 417605 | Aaron Browning | 1995-12-22 08:48:03 | [email protected] | 2015-09-01 16:41:20 |
| 74 | 883563 | Aaron Browning | 1998-09-21 17:14:16 | [email protected] | 2015-08-02 11:51:52 |
| 75 | 347786 | Aaron Browning | 2000-09-24 05:10:37 | [email protected] | 2016-02-08 14:32:22 |
| 76 | 511872 | Aaron Bruce | 1999-07-10 02:32:11 | In[email protected] | 2015-06-13 02:17:22 |
| 77 | 526648 | Aaron Buckley | 1982-09-10 19:13:35 | [email protected] | 2016-02-26 16:18:38 |
| 78 | 799606 | Aaron Bullock | 1995-08-01 07:57:22 | [email protected] | 2016-02-22 00:14:35 |
| 79 | 382172 | Aaron Bullock | 1987-08-26 14:36:14 | [email protected] | 2015-12-22 15:49:50 |
| 80 | 690515 | Aaron Burch | 2000-09-03 20:28:23 | [email protected] | 2015-04-05 23:08:28 |
+------+--------+----------------+---------------------+---------------------------------------------+---------------------+
20 rows in set (2 min 27.26 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; Using filesort |
| 2 | DERIVED | users | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
-- pakai cara pos, mau melihat halaman awal atau akhir, waktu yang dibutuhkan hampir sama.
-- jika digunakan untuk datatable yang memiliki sort by kolom, search, dan halaman yang dilihat hanya halaman2 awal, maka menggunakan cara biasa lebih cepat hasilnya didapatkan
Comments