-- 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 | consequat.lectus.sit@Nuncullamcorper.org | 2015-07-31 07:42:27 | | 689376 | Zorita Walsh | 1990-10-17 22:55:56 | fermentum@loremac.net | 2015-08-29 07:58:20 | | 785577 | Zorita Walter | 1985-11-16 05:52:37 | consectetuer.mauris@sapienCras.com | 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 | eu@tristique.net | 2015-07-27 03:34:34 | | 373327 | Zorita Ware | 1987-11-04 17:32:51 | amet@Phasellusdolorelit.ca | 2015-09-19 17:27:44 | | 789023 | Zorita Washington | 1998-10-14 01:55:58 | dictum@nuncullamcorper.com | 2015-05-03 14:48:36 | | 112559 | Zorita Washington | 1980-07-16 17:08:44 | adipiscing.elit@consequat.co.uk | 2015-02-25 23:11:41 | | 681705 | Zorita Webster | 1981-08-05 12:07:14 | convallis@blanditNamnulla.co.uk | 2015-05-30 15:02:23 | | 395543 | Zorita Weeks | 1982-08-20 04:23:21 | torquent.per.conubia@magnisdisparturient.net | 2015-12-13 07:40:03 | | 993066 | Zorita Weeks | 2000-05-03 15:43:28 | iaculis.aliquet.diam@interdumNuncsollicitudin.com | 2015-05-05 00:40:31 | | 181338 | Zorita Welch | 1995-01-21 09:01:06 | purus.in.molestie@dui.co.uk | 2015-09-24 22:26:52 | | 362332 | Zorita Welch | 2000-12-19 22:52:45 | gravida.mauris@velitAliquam.edu | 2015-10-15 11:35:38 | | 167832 | Zorita Welch | 1994-03-03 05:15:59 | semper.dui.lectus@mi.net | 2015-10-21 13:19:32 | | 501730 | Zorita Welch | 1998-05-28 19:29:57 | Sed.nulla.ante@aliquetnec.ca | 2015-08-04 14:03:28 | | 437806 | Zorita West | 1991-02-12 15:29:52 | enim@Suspendissecommodo.co.uk | 2015-05-07 00:46:19 | | 519870 | Zorita Whitaker | 1987-07-28 23:19:42 | eu.augue.porttitor@nonquamPellentesque.edu | 2015-11-19 03:56:00 | | 990047 | Zorita Whitehead | 1992-08-26 20:44:48 | nisl.Maecenas@hendrerit.org | 2015-05-18 23:34:23 | | 213451 | Zorita Whitehead | 2000-04-25 20:40:43 | est@loremsemperauctor.edu | 2015-08-12 06:08:08 | | 167061 | Zorita Whitney | 1984-09-10 13:42:37 | id@fringillacursus.ca | 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 | consequat.lectus.sit@Nuncullamcorper.org | 2015-07-31 07:42:27 | | 999962 | 689376 | Zorita Walsh | 1990-10-17 22:55:56 | fermentum@loremac.net | 2015-08-29 07:58:20 | | 999963 | 785577 | Zorita Walter | 1985-11-16 05:52:37 | consectetuer.mauris@sapienCras.com | 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 | eu@tristique.net | 2015-07-27 03:34:34 | | 999966 | 373327 | Zorita Ware | 1987-11-04 17:32:51 | amet@Phasellusdolorelit.ca | 2015-09-19 17:27:44 | | 999967 | 789023 | Zorita Washington | 1998-10-14 01:55:58 | dictum@nuncullamcorper.com | 2015-05-03 14:48:36 | | 999968 | 112559 | Zorita Washington | 1980-07-16 17:08:44 | adipiscing.elit@consequat.co.uk | 2015-02-25 23:11:41 | | 999969 | 681705 | Zorita Webster | 1981-08-05 12:07:14 | convallis@blanditNamnulla.co.uk | 2015-05-30 15:02:23 | | 999970 | 395543 | Zorita Weeks | 1982-08-20 04:23:21 | torquent.per.conubia@magnisdisparturient.net | 2015-12-13 07:40:03 | | 999971 | 993066 | Zorita Weeks | 2000-05-03 15:43:28 | iaculis.aliquet.diam@interdumNuncsollicitudin.com | 2015-05-05 00:40:31 | | 999972 | 181338 | Zorita Welch | 1995-01-21 09:01:06 | purus.in.molestie@dui.co.uk | 2015-09-24 22:26:52 | | 999973 | 362332 | Zorita Welch | 2000-12-19 22:52:45 | gravida.mauris@velitAliquam.edu | 2015-10-15 11:35:38 | | 999974 | 167832 | Zorita Welch | 1994-03-03 05:15:59 | semper.dui.lectus@mi.net | 2015-10-21 13:19:32 | | 999975 | 501730 | Zorita Welch | 1998-05-28 19:29:57 | Sed.nulla.ante@aliquetnec.ca | 2015-08-04 14:03:28 | | 999976 | 437806 | Zorita West | 1991-02-12 15:29:52 | enim@Suspendissecommodo.co.uk | 2015-05-07 00:46:19 | | 999977 | 519870 | Zorita Whitaker | 1987-07-28 23:19:42 | eu.augue.porttitor@nonquamPellentesque.edu | 2015-11-19 03:56:00 | | 999978 | 990047 | Zorita Whitehead | 1992-08-26 20:44:48 | nisl.Maecenas@hendrerit.org | 2015-05-18 23:34:23 | | 999979 | 213451 | Zorita Whitehead | 2000-04-25 20:40:43 | est@loremsemperauctor.edu | 2015-08-12 06:08:08 | | 999980 | 167061 | Zorita Whitney | 1984-09-10 13:42:37 | id@fringillacursus.ca | 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 | ut@nullaIn.ca | 2015-01-23 15:21:05 | | 734226 | Aaron Benton | 1981-06-05 16:33:39 | consectetuer@euismodenim.co.uk | 2015-10-29 22:01:27 | | 673274 | Aaron Benson | 1995-07-22 04:58:13 | ullamcorper@Nunclectus.co.uk | 2016-01-01 14:50:53 | | 134255 | Aaron Bennett | 1989-08-22 13:31:34 | eu.eros@nectellusNunc.ca | 2015-09-15 13:28:26 | | 841412 | Aaron Bennett | 1983-04-12 10:56:38 | feugiat.Lorem.ipsum@Aliquameratvolutpat.edu | 2015-07-09 01:56:28 | | 64031 | Aaron Benjamin | 1981-04-20 16:24:33 | dictum.cursus.Nunc@adipiscingMaurismolestie.ca | 2015-06-13 02:07:37 | | 181292 | Aaron Benjamin | 1982-11-15 06:59:12 | Sed.nec.metus@malesuadavelvenenatis.ca | 2015-10-26 21:10:27 | | 840189 | Aaron Bender | 1997-09-22 01:39:04 | convallis.ante.lectus@etlaciniavitae.ca | 2015-10-05 22:42:04 | | 370860 | Aaron Becker | 1986-04-02 05:48:27 | tortor.dictum@Proineget.edu | 2015-01-27 03:12:10 | | 229737 | Aaron Beasley | 1986-10-16 07:44:46 | lorem@nisi.net | 2015-03-01 06:38:21 | | 789904 | Aaron Beasley | 1984-05-12 16:57:10 | dolor.Nulla.semper@rutrumeu.ca | 2016-02-24 00:15:19 | | 773388 | Aaron Beard | 1986-03-25 03:32:17 | amet.risus.Donec@tristique.ca | 2015-12-07 12:53:11 | | 171623 | Aaron Beard | 1992-07-29 18:52:29 | quam.elementum.at@Nullamfeugiatplacerat.edu | 2015-06-15 01:45:45 | | 658446 | Aaron Bean | 1989-03-13 15:54:56 | est@eutempor.edu | 2015-06-07 07:52:42 | | 407688 | Aaron Baxter | 1983-03-26 19:19:01 | sagittis@nisiCum.ca | 2015-09-03 20:55:30 | | 924833 | Aaron Bauer | 1999-12-11 13:28:22 | Curabitur@nonenimMauris.edu | 2016-01-19 00:30:24 | | 777753 | Aaron Battle | 1997-11-24 21:24:09 | id.mollis@ac.co.uk | 2015-05-08 00:17:21 | | 217447 | Aaron Bass | 1992-12-27 15:59:17 | felis.purus.ac@urnajusto.co.uk | 2015-01-06 05:07:39 | | 732683 | Aaron Bartlett | 1984-03-07 10:22:55 | Quisque.libero@etultrices.co.uk | 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 | ut@nullaIn.ca | 2015-01-23 15:21:05 | | 999962 | 734226 | Aaron Benton | 1981-06-05 16:33:39 | consectetuer@euismodenim.co.uk | 2015-10-29 22:01:27 | | 999963 | 673274 | Aaron Benson | 1995-07-22 04:58:13 | ullamcorper@Nunclectus.co.uk | 2016-01-01 14:50:53 | | 999964 | 134255 | Aaron Bennett | 1989-08-22 13:31:34 | eu.eros@nectellusNunc.ca | 2015-09-15 13:28:26 | | 999965 | 841412 | Aaron Bennett | 1983-04-12 10:56:38 | feugiat.Lorem.ipsum@Aliquameratvolutpat.edu | 2015-07-09 01:56:28 | | 999966 | 64031 | Aaron Benjamin | 1981-04-20 16:24:33 | dictum.cursus.Nunc@adipiscingMaurismolestie.ca | 2015-06-13 02:07:37 | | 999967 | 181292 | Aaron Benjamin | 1982-11-15 06:59:12 | Sed.nec.metus@malesuadavelvenenatis.ca | 2015-10-26 21:10:27 | | 999968 | 840189 | Aaron Bender | 1997-09-22 01:39:04 | convallis.ante.lectus@etlaciniavitae.ca | 2015-10-05 22:42:04 | | 999969 | 370860 | Aaron Becker | 1986-04-02 05:48:27 | tortor.dictum@Proineget.edu | 2015-01-27 03:12:10 | | 999970 | 229737 | Aaron Beasley | 1986-10-16 07:44:46 | lorem@nisi.net | 2015-03-01 06:38:21 | | 999971 | 789904 | Aaron Beasley | 1984-05-12 16:57:10 | dolor.Nulla.semper@rutrumeu.ca | 2016-02-24 00:15:19 | | 999972 | 773388 | Aaron Beard | 1986-03-25 03:32:17 | amet.risus.Donec@tristique.ca | 2015-12-07 12:53:11 | | 999973 | 171623 | Aaron Beard | 1992-07-29 18:52:29 | quam.elementum.at@Nullamfeugiatplacerat.edu | 2015-06-15 01:45:45 | | 999974 | 658446 | Aaron Bean | 1989-03-13 15:54:56 | est@eutempor.edu | 2015-06-07 07:52:42 | | 999975 | 407688 | Aaron Baxter | 1983-03-26 19:19:01 | sagittis@nisiCum.ca | 2015-09-03 20:55:30 | | 999976 | 924833 | Aaron Bauer | 1999-12-11 13:28:22 | Curabitur@nonenimMauris.edu | 2016-01-19 00:30:24 | | 999977 | 777753 | Aaron Battle | 1997-11-24 21:24:09 | id.mollis@ac.co.uk | 2015-05-08 00:17:21 | | 999978 | 217447 | Aaron Bass | 1992-12-27 15:59:17 | felis.purus.ac@urnajusto.co.uk | 2015-01-06 05:07:39 | | 999979 | 732683 | Aaron Bartlett | 1984-03-07 10:22:55 | Quisque.libero@etultrices.co.uk | 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 | consequat.lectus.sit@Nuncullamcorper.org | 2015-07-31 07:42:27 | | 689376 | Zorita Walsh | 1990-10-17 22:55:56 | fermentum@loremac.net | 2015-08-29 07:58:20 | | 785577 | Zorita Walter | 1985-11-16 05:52:37 | consectetuer.mauris@sapienCras.com | 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 | eu@tristique.net | 2015-07-27 03:34:34 | | 373327 | Zorita Ware | 1987-11-04 17:32:51 | amet@Phasellusdolorelit.ca | 2015-09-19 17:27:44 | | 789023 | Zorita Washington | 1998-10-14 01:55:58 | dictum@nuncullamcorper.com | 2015-05-03 14:48:36 | | 112559 | Zorita Washington | 1980-07-16 17:08:44 | adipiscing.elit@consequat.co.uk | 2015-02-25 23:11:41 | | 681705 | Zorita Webster | 1981-08-05 12:07:14 | convallis@blanditNamnulla.co.uk | 2015-05-30 15:02:23 | | 395543 | Zorita Weeks | 1982-08-20 04:23:21 | torquent.per.conubia@magnisdisparturient.net | 2015-12-13 07:40:03 | | 993066 | Zorita Weeks | 2000-05-03 15:43:28 | iaculis.aliquet.diam@interdumNuncsollicitudin.com | 2015-05-05 00:40:31 | | 181338 | Zorita Welch | 1995-01-21 09:01:06 | purus.in.molestie@dui.co.uk | 2015-09-24 22:26:52 | | 362332 | Zorita Welch | 2000-12-19 22:52:45 | gravida.mauris@velitAliquam.edu | 2015-10-15 11:35:38 | | 167832 | Zorita Welch | 1994-03-03 05:15:59 | semper.dui.lectus@mi.net | 2015-10-21 13:19:32 | | 501730 | Zorita Welch | 1998-05-28 19:29:57 | Sed.nulla.ante@aliquetnec.ca | 2015-08-04 14:03:28 | | 437806 | Zorita West | 1991-02-12 15:29:52 | enim@Suspendissecommodo.co.uk | 2015-05-07 00:46:19 | | 519870 | Zorita Whitaker | 1987-07-28 23:19:42 | eu.augue.porttitor@nonquamPellentesque.edu | 2015-11-19 03:56:00 | | 990047 | Zorita Whitehead | 1992-08-26 20:44:48 | nisl.Maecenas@hendrerit.org | 2015-05-18 23:34:23 | | 213451 | Zorita Whitehead | 2000-04-25 20:40:43 | est@loremsemperauctor.edu | 2015-08-12 06:08:08 | | 167061 | Zorita Whitney | 1984-09-10 13:42:37 | id@fringillacursus.ca | 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 | consequat.lectus.sit@Nuncullamcorper.org | 2015-07-31 07:42:27 | | 999962 | 689376 | Zorita Walsh | 1990-10-17 22:55:56 | fermentum@loremac.net | 2015-08-29 07:58:20 | | 999963 | 785577 | Zorita Walter | 1985-11-16 05:52:37 | consectetuer.mauris@sapienCras.com | 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 | eu@tristique.net | 2015-07-27 03:34:34 | | 999966 | 373327 | Zorita Ware | 1987-11-04 17:32:51 | amet@Phasellusdolorelit.ca | 2015-09-19 17:27:44 | | 999967 | 789023 | Zorita Washington | 1998-10-14 01:55:58 | dictum@nuncullamcorper.com | 2015-05-03 14:48:36 | | 999968 | 112559 | Zorita Washington | 1980-07-16 17:08:44 | adipiscing.elit@consequat.co.uk | 2015-02-25 23:11:41 | | 999969 | 681705 | Zorita Webster | 1981-08-05 12:07:14 | convallis@blanditNamnulla.co.uk | 2015-05-30 15:02:23 | | 999970 | 395543 | Zorita Weeks | 1982-08-20 04:23:21 | torquent.per.conubia@magnisdisparturient.net | 2015-12-13 07:40:03 | | 999971 | 993066 | Zorita Weeks | 2000-05-03 15:43:28 | iaculis.aliquet.diam@interdumNuncsollicitudin.com | 2015-05-05 00:40:31 | | 999972 | 181338 | Zorita Welch | 1995-01-21 09:01:06 | purus.in.molestie@dui.co.uk | 2015-09-24 22:26:52 | | 999973 | 362332 | Zorita Welch | 2000-12-19 22:52:45 | gravida.mauris@velitAliquam.edu | 2015-10-15 11:35:38 | | 999974 | 167832 | Zorita Welch | 1994-03-03 05:15:59 | semper.dui.lectus@mi.net | 2015-10-21 13:19:32 | | 999975 | 501730 | Zorita Welch | 1998-05-28 19:29:57 | Sed.nulla.ante@aliquetnec.ca | 2015-08-04 14:03:28 | | 999976 | 437806 | Zorita West | 1991-02-12 15:29:52 | enim@Suspendissecommodo.co.uk | 2015-05-07 00:46:19 | | 999977 | 519870 | Zorita Whitaker | 1987-07-28 23:19:42 | eu.augue.porttitor@nonquamPellentesque.edu | 2015-11-19 03:56:00 | | 999978 | 990047 | Zorita Whitehead | 1992-08-26 20:44:48 | nisl.Maecenas@hendrerit.org | 2015-05-18 23:34:23 | | 999979 | 213451 | Zorita Whitehead | 2000-04-25 20:40:43 | est@loremsemperauctor.edu | 2015-08-12 06:08:08 | | 999980 | 167061 | Zorita Whitney | 1984-09-10 13:42:37 | id@fringillacursus.ca | 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 | | 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 | ut@nullaIn.ca | 2015-01-23 15:21:05 | | 734226 | Aaron Benton | 1981-06-05 16:33:39 | consectetuer@euismodenim.co.uk | 2015-10-29 22:01:27 | | 673274 | Aaron Benson | 1995-07-22 04:58:13 | ullamcorper@Nunclectus.co.uk | 2016-01-01 14:50:53 | | 134255 | Aaron Bennett | 1989-08-22 13:31:34 | eu.eros@nectellusNunc.ca | 2015-09-15 13:28:26 | | 841412 | Aaron Bennett | 1983-04-12 10:56:38 | feugiat.Lorem.ipsum@Aliquameratvolutpat.edu | 2015-07-09 01:56:28 | | 64031 | Aaron Benjamin | 1981-04-20 16:24:33 | dictum.cursus.Nunc@adipiscingMaurismolestie.ca | 2015-06-13 02:07:37 | | 181292 | Aaron Benjamin | 1982-11-15 06:59:12 | Sed.nec.metus@malesuadavelvenenatis.ca | 2015-10-26 21:10:27 | | 840189 | Aaron Bender | 1997-09-22 01:39:04 | convallis.ante.lectus@etlaciniavitae.ca | 2015-10-05 22:42:04 | | 370860 | Aaron Becker | 1986-04-02 05:48:27 | tortor.dictum@Proineget.edu | 2015-01-27 03:12:10 | | 229737 | Aaron Beasley | 1986-10-16 07:44:46 | lorem@nisi.net | 2015-03-01 06:38:21 | | 789904 | Aaron Beasley | 1984-05-12 16:57:10 | dolor.Nulla.semper@rutrumeu.ca | 2016-02-24 00:15:19 | | 773388 | Aaron Beard | 1986-03-25 03:32:17 | amet.risus.Donec@tristique.ca | 2015-12-07 12:53:11 | | 171623 | Aaron Beard | 1992-07-29 18:52:29 | quam.elementum.at@Nullamfeugiatplacerat.edu | 2015-06-15 01:45:45 | | 658446 | Aaron Bean | 1989-03-13 15:54:56 | est@eutempor.edu | 2015-06-07 07:52:42 | | 407688 | Aaron Baxter | 1983-03-26 19:19:01 | sagittis@nisiCum.ca | 2015-09-03 20:55:30 | | 924833 | Aaron Bauer | 1999-12-11 13:28:22 | Curabitur@nonenimMauris.edu | 2016-01-19 00:30:24 | | 777753 | Aaron Battle | 1997-11-24 21:24:09 | id.mollis@ac.co.uk | 2015-05-08 00:17:21 | | 217447 | Aaron Bass | 1992-12-27 15:59:17 | felis.purus.ac@urnajusto.co.uk | 2015-01-06 05:07:39 | | 732683 | Aaron Bartlett | 1984-03-07 10:22:55 | Quisque.libero@etultrices.co.uk | 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 | ut@nullaIn.ca | 2015-01-23 15:21:05 | | 999962 | 734226 | Aaron Benton | 1981-06-05 16:33:39 | consectetuer@euismodenim.co.uk | 2015-10-29 22:01:27 | | 999963 | 673274 | Aaron Benson | 1995-07-22 04:58:13 | ullamcorper@Nunclectus.co.uk | 2016-01-01 14:50:53 | | 999964 | 134255 | Aaron Bennett | 1989-08-22 13:31:34 | eu.eros@nectellusNunc.ca | 2015-09-15 13:28:26 | | 999965 | 841412 | Aaron Bennett | 1983-04-12 10:56:38 | feugiat.Lorem.ipsum@Aliquameratvolutpat.edu | 2015-07-09 01:56:28 | | 999966 | 64031 | Aaron Benjamin | 1981-04-20 16:24:33 | dictum.cursus.Nunc@adipiscingMaurismolestie.ca | 2015-06-13 02:07:37 | | 999967 | 181292 | Aaron Benjamin | 1982-11-15 06:59:12 | Sed.nec.metus@malesuadavelvenenatis.ca | 2015-10-26 21:10:27 | | 999968 | 840189 | Aaron Bender | 1997-09-22 01:39:04 | convallis.ante.lectus@etlaciniavitae.ca | 2015-10-05 22:42:04 | | 999969 | 370860 | Aaron Becker | 1986-04-02 05:48:27 | tortor.dictum@Proineget.edu | 2015-01-27 03:12:10 | | 999970 | 229737 | Aaron Beasley | 1986-10-16 07:44:46 | lorem@nisi.net | 2015-03-01 06:38:21 | | 999971 | 789904 | Aaron Beasley | 1984-05-12 16:57:10 | dolor.Nulla.semper@rutrumeu.ca | 2016-02-24 00:15:19 | | 999972 | 773388 | Aaron Beard | 1986-03-25 03:32:17 | amet.risus.Donec@tristique.ca | 2015-12-07 12:53:11 | | 999973 | 171623 | Aaron Beard | 1992-07-29 18:52:29 | quam.elementum.at@Nullamfeugiatplacerat.edu | 2015-06-15 01:45:45 | | 999974 | 658446 | Aaron Bean | 1989-03-13 15:54:56 | est@eutempor.edu | 2015-06-07 07:52:42 | | 999975 | 407688 | Aaron Baxter | 1983-03-26 19:19:01 | sagittis@nisiCum.ca | 2015-09-03 20:55:30 | | 999976 | 924833 | Aaron Bauer | 1999-12-11 13:28:22 | Curabitur@nonenimMauris.edu | 2016-01-19 00:30:24 | | 999977 | 777753 | Aaron Battle | 1997-11-24 21:24:09 | id.mollis@ac.co.uk | 2015-05-08 00:17:21 | | 999978 | 217447 | Aaron Bass | 1992-12-27 15:59:17 | felis.purus.ac@urnajusto.co.uk | 2015-01-06 05:07:39 | | 999979 | 732683 | Aaron Bartlett | 1984-03-07 10:22:55 | Quisque.libero@etultrices.co.uk | 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 | | 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 | sem.ut.cursus@quisdiamPellentesque.net | 2015-04-24 00:57:26 | | 939018 | Aaron Bray | 1991-07-11 22:53:15 | aliquet.Proin.velit@Curabiturvellectus.ca | 2015-03-14 00:51:30 | | 32163 | Aaron Brennan | 1981-07-11 20:24:29 | libero@torquentperconubia.ca | 2015-11-08 03:25:29 | | 502845 | Aaron Brennan | 1980-04-08 07:50:48 | ut.mi.Duis@dolorelitpellentesque.org | 2015-08-13 18:44:07 | | 556419 | Aaron Brennan | 1997-07-04 10:16:25 | pede@placerat.edu | 2015-11-29 20:20:01 | | 938914 | Aaron Brennan | 1982-01-29 12:45:45 | non.luctus@iaculisquis.edu | 2015-07-14 21:53:57 | | 163125 | Aaron Brewer | 1996-10-22 18:12:20 | ultrices.iaculis@velitjustonec.com | 2015-01-27 09:39:47 | | 819358 | Aaron Brewer | 1995-05-24 12:49:46 | dictum.eu@primis.net | 2015-10-26 02:44:15 | | 220648 | Aaron Britt | 1985-02-23 13:00:06 | leo@massaQuisque.org | 2015-08-20 02:56:57 | | 872558 | Aaron Brock | 1984-09-30 04:49:56 | interdum.feugiat@venenatis.com | 2015-04-14 11:47:32 | | 447693 | Aaron Brown | 1985-08-28 02:04:12 | montes.nascetur@eratVivamusnisi.net | 2015-06-19 05:35:39 | | 79683 | Aaron Browning | 1980-11-12 15:59:30 | ornare@inaliquet.co.uk | 2015-01-16 23:42:54 | | 347786 | Aaron Browning | 2000-09-24 05:10:37 | lobortis.quam.a@Vestibulumaccumsanneque.net | 2016-02-08 14:32:22 | | 417605 | Aaron Browning | 1995-12-22 08:48:03 | sem.Pellentesque@euodioPhasellus.net | 2015-09-01 16:41:20 | | 883563 | Aaron Browning | 1998-09-21 17:14:16 | eleifend@arcuVestibulum.ca | 2015-08-02 11:51:52 | | 511872 | Aaron Bruce | 1999-07-10 02:32:11 | In.at.pede@imperdiet.co.uk | 2015-06-13 02:17:22 | | 526648 | Aaron Buckley | 1982-09-10 19:13:35 | nonummy@dictumauguemalesuada.com | 2016-02-26 16:18:38 | | 382172 | Aaron Bullock | 1987-08-26 14:36:14 | sagittis.augue.eu@nec.org | 2015-12-22 15:49:50 | | 799606 | Aaron Bullock | 1995-08-01 07:57:22 | libero.lacus@nec.co.uk | 2016-02-22 00:14:35 | | 690515 | Aaron Burch | 2000-09-03 20:28:23 | sodales.Mauris@molestiepharetra.org | 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 | sem.ut.cursus@quisdiamPellentesque.net | 2015-04-24 00:57:26 | | 62 | 939018 | Aaron Bray | 1991-07-11 22:53:15 | aliquet.Proin.velit@Curabiturvellectus.ca | 2015-03-14 00:51:30 | | 63 | 502845 | Aaron Brennan | 1980-04-08 07:50:48 | ut.mi.Duis@dolorelitpellentesque.org | 2015-08-13 18:44:07 | | 64 | 556419 | Aaron Brennan | 1997-07-04 10:16:25 | pede@placerat.edu | 2015-11-29 20:20:01 | | 65 | 938914 | Aaron Brennan | 1982-01-29 12:45:45 | non.luctus@iaculisquis.edu | 2015-07-14 21:53:57 | | 66 | 32163 | Aaron Brennan | 1981-07-11 20:24:29 | libero@torquentperconubia.ca | 2015-11-08 03:25:29 | | 67 | 163125 | Aaron Brewer | 1996-10-22 18:12:20 | ultrices.iaculis@velitjustonec.com | 2015-01-27 09:39:47 | | 68 | 819358 | Aaron Brewer | 1995-05-24 12:49:46 | dictum.eu@primis.net | 2015-10-26 02:44:15 | | 69 | 220648 | Aaron Britt | 1985-02-23 13:00:06 | leo@massaQuisque.org | 2015-08-20 02:56:57 | | 70 | 872558 | Aaron Brock | 1984-09-30 04:49:56 | interdum.feugiat@venenatis.com | 2015-04-14 11:47:32 | | 71 | 447693 | Aaron Brown | 1985-08-28 02:04:12 | montes.nascetur@eratVivamusnisi.net | 2015-06-19 05:35:39 | | 72 | 79683 | Aaron Browning | 1980-11-12 15:59:30 | ornare@inaliquet.co.uk | 2015-01-16 23:42:54 | | 73 | 417605 | Aaron Browning | 1995-12-22 08:48:03 | sem.Pellentesque@euodioPhasellus.net | 2015-09-01 16:41:20 | | 74 | 883563 | Aaron Browning | 1998-09-21 17:14:16 | eleifend@arcuVestibulum.ca | 2015-08-02 11:51:52 | | 75 | 347786 | Aaron Browning | 2000-09-24 05:10:37 | lobortis.quam.a@Vestibulumaccumsanneque.net | 2016-02-08 14:32:22 | | 76 | 511872 | Aaron Bruce | 1999-07-10 02:32:11 | In.at.pede@imperdiet.co.uk | 2015-06-13 02:17:22 | | 77 | 526648 | Aaron Buckley | 1982-09-10 19:13:35 | nonummy@dictumauguemalesuada.com | 2016-02-26 16:18:38 | | 78 | 799606 | Aaron Bullock | 1995-08-01 07:57:22 | libero.lacus@nec.co.uk | 2016-02-22 00:14:35 | | 79 | 382172 | Aaron Bullock | 1987-08-26 14:36:14 | sagittis.augue.eu@nec.org | 2015-12-22 15:49:50 | | 80 | 690515 | Aaron Burch | 2000-09-03 20:28:23 | sodales.Mauris@molestiepharetra.org | 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 | | 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