· 10 years ago · Mar 12, 2016, 11:51 AM
1-- struktur tabel:
2-- DROP TABLE IF EXISTS `users`;
3
4-- CREATE TABLE `users` (
5-- `id` mediumint(8) unsigned NOT NULL auto_increment,
6-- `full_name` varchar(255) default NULL,
7-- `birth_date` varchar(255),
8-- `email` varchar(255) default NULL,
9-- `register_date` varchar(255),
10-- PRIMARY KEY (`id`)
11-- ) AUTO_INCREMENT=1;
12
13-- total data
14SELECT COUNT(*)
15 FROM `users`;
16-- hasil
17+----------+
18| COUNT(*) |
19+----------+
20| 1000000 |
21+----------+
221 row in set (0.16 sec)
23
24-- data sejumlah 1juta row, per halaman 20 data, berarti total ada 50ribu data
25-- sekarang coba kita ambil data di halaman 49.999 sebanyak 20 data
26-- offset = ( `49999 - 1 ) * 20 = 999960
27
28-- tanpa index
29-- cara biasa, ambil data urut full_name
30 SELECT `id`, `full_name`, `birth_date`, `email`, `register_date`
31 FROM `users`
32ORDER BY `full_name` ASC
33 LIMIT 999960, 20;
34-- hasil
35+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
36| id | full_name | birth_date | email | register_date |
37+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
38| 711969 | Zorita Walls | 1989-11-29 07:11:47 | consequat.lectus.sit@Nuncullamcorper.org | 2015-07-31 07:42:27 |
39| 689376 | Zorita Walsh | 1990-10-17 22:55:56 | fermentum@loremac.net | 2015-08-29 07:58:20 |
40| 785577 | Zorita Walter | 1985-11-16 05:52:37 | consectetuer.mauris@sapienCras.com | 2016-02-22 07:00:58 |
41| 992994 | Zorita Walton | 1992-04-04 21:13:24 | In@sit.com | 2015-10-09 09:39:23 |
42| 139823 | Zorita Ware | 1993-06-25 05:16:48 | eu@tristique.net | 2015-07-27 03:34:34 |
43| 373327 | Zorita Ware | 1987-11-04 17:32:51 | amet@Phasellusdolorelit.ca | 2015-09-19 17:27:44 |
44| 789023 | Zorita Washington | 1998-10-14 01:55:58 | dictum@nuncullamcorper.com | 2015-05-03 14:48:36 |
45| 112559 | Zorita Washington | 1980-07-16 17:08:44 | adipiscing.elit@consequat.co.uk | 2015-02-25 23:11:41 |
46| 681705 | Zorita Webster | 1981-08-05 12:07:14 | convallis@blanditNamnulla.co.uk | 2015-05-30 15:02:23 |
47| 395543 | Zorita Weeks | 1982-08-20 04:23:21 | torquent.per.conubia@magnisdisparturient.net | 2015-12-13 07:40:03 |
48| 993066 | Zorita Weeks | 2000-05-03 15:43:28 | iaculis.aliquet.diam@interdumNuncsollicitudin.com | 2015-05-05 00:40:31 |
49| 181338 | Zorita Welch | 1995-01-21 09:01:06 | purus.in.molestie@dui.co.uk | 2015-09-24 22:26:52 |
50| 362332 | Zorita Welch | 2000-12-19 22:52:45 | gravida.mauris@velitAliquam.edu | 2015-10-15 11:35:38 |
51| 167832 | Zorita Welch | 1994-03-03 05:15:59 | semper.dui.lectus@mi.net | 2015-10-21 13:19:32 |
52| 501730 | Zorita Welch | 1998-05-28 19:29:57 | Sed.nulla.ante@aliquetnec.ca | 2015-08-04 14:03:28 |
53| 437806 | Zorita West | 1991-02-12 15:29:52 | enim@Suspendissecommodo.co.uk | 2015-05-07 00:46:19 |
54| 519870 | Zorita Whitaker | 1987-07-28 23:19:42 | eu.augue.porttitor@nonquamPellentesque.edu | 2015-11-19 03:56:00 |
55| 990047 | Zorita Whitehead | 1992-08-26 20:44:48 | nisl.Maecenas@hendrerit.org | 2015-05-18 23:34:23 |
56| 213451 | Zorita Whitehead | 2000-04-25 20:40:43 | est@loremsemperauctor.edu | 2015-08-12 06:08:08 |
57| 167061 | Zorita Whitney | 1984-09-10 13:42:37 | id@fringillacursus.ca | 2015-10-03 15:50:01 |
58+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
5920 rows in set (1 min 49.39 sec)
60
61-- pakai posisi
62SET @row_number=0;
63SELECT *
64 FROM (
65 SELECT @row_number:=@row_number+1 AS `pos`,
66 `id`, `full_name`, `birth_date`, `email`, `register_date`
67 FROM `users`
68 ORDER BY `full_name` ASC
69 ) `users_with_pos`
70 WHERE `pos` > 999960
71 ORDER BY `pos` ASC
72 LIMIT 20;
73-- hasil
74+--------+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
75| pos | id | full_name | birth_date | email | register_date |
76+--------+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
77| 999961 | 711969 | Zorita Walls | 1989-11-29 07:11:47 | consequat.lectus.sit@Nuncullamcorper.org | 2015-07-31 07:42:27 |
78| 999962 | 689376 | Zorita Walsh | 1990-10-17 22:55:56 | fermentum@loremac.net | 2015-08-29 07:58:20 |
79| 999963 | 785577 | Zorita Walter | 1985-11-16 05:52:37 | consectetuer.mauris@sapienCras.com | 2016-02-22 07:00:58 |
80| 999964 | 992994 | Zorita Walton | 1992-04-04 21:13:24 | In@sit.com | 2015-10-09 09:39:23 |
81| 999965 | 139823 | Zorita Ware | 1993-06-25 05:16:48 | eu@tristique.net | 2015-07-27 03:34:34 |
82| 999966 | 373327 | Zorita Ware | 1987-11-04 17:32:51 | amet@Phasellusdolorelit.ca | 2015-09-19 17:27:44 |
83| 999967 | 789023 | Zorita Washington | 1998-10-14 01:55:58 | dictum@nuncullamcorper.com | 2015-05-03 14:48:36 |
84| 999968 | 112559 | Zorita Washington | 1980-07-16 17:08:44 | adipiscing.elit@consequat.co.uk | 2015-02-25 23:11:41 |
85| 999969 | 681705 | Zorita Webster | 1981-08-05 12:07:14 | convallis@blanditNamnulla.co.uk | 2015-05-30 15:02:23 |
86| 999970 | 395543 | Zorita Weeks | 1982-08-20 04:23:21 | torquent.per.conubia@magnisdisparturient.net | 2015-12-13 07:40:03 |
87| 999971 | 993066 | Zorita Weeks | 2000-05-03 15:43:28 | iaculis.aliquet.diam@interdumNuncsollicitudin.com | 2015-05-05 00:40:31 |
88| 999972 | 181338 | Zorita Welch | 1995-01-21 09:01:06 | purus.in.molestie@dui.co.uk | 2015-09-24 22:26:52 |
89| 999973 | 362332 | Zorita Welch | 2000-12-19 22:52:45 | gravida.mauris@velitAliquam.edu | 2015-10-15 11:35:38 |
90| 999974 | 167832 | Zorita Welch | 1994-03-03 05:15:59 | semper.dui.lectus@mi.net | 2015-10-21 13:19:32 |
91| 999975 | 501730 | Zorita Welch | 1998-05-28 19:29:57 | Sed.nulla.ante@aliquetnec.ca | 2015-08-04 14:03:28 |
92| 999976 | 437806 | Zorita West | 1991-02-12 15:29:52 | enim@Suspendissecommodo.co.uk | 2015-05-07 00:46:19 |
93| 999977 | 519870 | Zorita Whitaker | 1987-07-28 23:19:42 | eu.augue.porttitor@nonquamPellentesque.edu | 2015-11-19 03:56:00 |
94| 999978 | 990047 | Zorita Whitehead | 1992-08-26 20:44:48 | nisl.Maecenas@hendrerit.org | 2015-05-18 23:34:23 |
95| 999979 | 213451 | Zorita Whitehead | 2000-04-25 20:40:43 | est@loremsemperauctor.edu | 2015-08-12 06:08:08 |
96| 999980 | 167061 | Zorita Whitney | 1984-09-10 13:42:37 | id@fringillacursus.ca | 2015-10-03 15:50:01 |
97+--------+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
9820 rows in set (1 min 31.27 sec)
99-- catatan: bedanya hanya 18.12 sec. Memang pakai posisi jadi sedikit lebih cepat
100
101-- desc
102 SELECT `id`, `full_name`, `birth_date`, `email`, `register_date`
103 FROM `users`
104ORDER BY `full_name` DESC
105 LIMIT 999960, 20;
106-- hasil
107+--------+----------------+---------------------+------------------------------------------------+---------------------+
108| id | full_name | birth_date | email | register_date |
109+--------+----------------+---------------------+------------------------------------------------+---------------------+
110| 319574 | Aaron Berg | 1988-05-31 15:27:22 | ut@nullaIn.ca | 2015-01-23 15:21:05 |
111| 734226 | Aaron Benton | 1981-06-05 16:33:39 | consectetuer@euismodenim.co.uk | 2015-10-29 22:01:27 |
112| 673274 | Aaron Benson | 1995-07-22 04:58:13 | ullamcorper@Nunclectus.co.uk | 2016-01-01 14:50:53 |
113| 134255 | Aaron Bennett | 1989-08-22 13:31:34 | eu.eros@nectellusNunc.ca | 2015-09-15 13:28:26 |
114| 841412 | Aaron Bennett | 1983-04-12 10:56:38 | feugiat.Lorem.ipsum@Aliquameratvolutpat.edu | 2015-07-09 01:56:28 |
115| 64031 | Aaron Benjamin | 1981-04-20 16:24:33 | dictum.cursus.Nunc@adipiscingMaurismolestie.ca | 2015-06-13 02:07:37 |
116| 181292 | Aaron Benjamin | 1982-11-15 06:59:12 | Sed.nec.metus@malesuadavelvenenatis.ca | 2015-10-26 21:10:27 |
117| 840189 | Aaron Bender | 1997-09-22 01:39:04 | convallis.ante.lectus@etlaciniavitae.ca | 2015-10-05 22:42:04 |
118| 370860 | Aaron Becker | 1986-04-02 05:48:27 | tortor.dictum@Proineget.edu | 2015-01-27 03:12:10 |
119| 229737 | Aaron Beasley | 1986-10-16 07:44:46 | lorem@nisi.net | 2015-03-01 06:38:21 |
120| 789904 | Aaron Beasley | 1984-05-12 16:57:10 | dolor.Nulla.semper@rutrumeu.ca | 2016-02-24 00:15:19 |
121| 773388 | Aaron Beard | 1986-03-25 03:32:17 | amet.risus.Donec@tristique.ca | 2015-12-07 12:53:11 |
122| 171623 | Aaron Beard | 1992-07-29 18:52:29 | quam.elementum.at@Nullamfeugiatplacerat.edu | 2015-06-15 01:45:45 |
123| 658446 | Aaron Bean | 1989-03-13 15:54:56 | est@eutempor.edu | 2015-06-07 07:52:42 |
124| 407688 | Aaron Baxter | 1983-03-26 19:19:01 | sagittis@nisiCum.ca | 2015-09-03 20:55:30 |
125| 924833 | Aaron Bauer | 1999-12-11 13:28:22 | Curabitur@nonenimMauris.edu | 2016-01-19 00:30:24 |
126| 777753 | Aaron Battle | 1997-11-24 21:24:09 | id.mollis@ac.co.uk | 2015-05-08 00:17:21 |
127| 217447 | Aaron Bass | 1992-12-27 15:59:17 | felis.purus.ac@urnajusto.co.uk | 2015-01-06 05:07:39 |
128| 732683 | Aaron Bartlett | 1984-03-07 10:22:55 | Quisque.libero@etultrices.co.uk | 2016-02-08 13:03:43 |
129| 158931 | Aaron Bartlett | 1997-07-23 00:43:30 | libero.Integer.in@risusDonec.ca | 2016-01-12 04:29:50 |
130+--------+----------------+---------------------+------------------------------------------------+---------------------+
13120 rows in set (2 min 17.52 sec)
132
133SET @row_number=0;
134SELECT *
135 FROM (
136 SELECT @row_number:=@row_number+1 AS `pos`,
137 `id`, `full_name`, `birth_date`, `email`, `register_date`
138 FROM `users`
139 ORDER BY `full_name` DESC
140 ) `users_with_pos`
141 WHERE `pos` > 999960
142ORDER BY `pos` ASC
143 LIMIT 20;
144-- hasil
145+--------+--------+----------------+---------------------+------------------------------------------------+---------------------+
146| pos | id | full_name | birth_date | email | register_date |
147+--------+--------+----------------+---------------------+------------------------------------------------+---------------------+
148| 999961 | 319574 | Aaron Berg | 1988-05-31 15:27:22 | ut@nullaIn.ca | 2015-01-23 15:21:05 |
149| 999962 | 734226 | Aaron Benton | 1981-06-05 16:33:39 | consectetuer@euismodenim.co.uk | 2015-10-29 22:01:27 |
150| 999963 | 673274 | Aaron Benson | 1995-07-22 04:58:13 | ullamcorper@Nunclectus.co.uk | 2016-01-01 14:50:53 |
151| 999964 | 134255 | Aaron Bennett | 1989-08-22 13:31:34 | eu.eros@nectellusNunc.ca | 2015-09-15 13:28:26 |
152| 999965 | 841412 | Aaron Bennett | 1983-04-12 10:56:38 | feugiat.Lorem.ipsum@Aliquameratvolutpat.edu | 2015-07-09 01:56:28 |
153| 999966 | 64031 | Aaron Benjamin | 1981-04-20 16:24:33 | dictum.cursus.Nunc@adipiscingMaurismolestie.ca | 2015-06-13 02:07:37 |
154| 999967 | 181292 | Aaron Benjamin | 1982-11-15 06:59:12 | Sed.nec.metus@malesuadavelvenenatis.ca | 2015-10-26 21:10:27 |
155| 999968 | 840189 | Aaron Bender | 1997-09-22 01:39:04 | convallis.ante.lectus@etlaciniavitae.ca | 2015-10-05 22:42:04 |
156| 999969 | 370860 | Aaron Becker | 1986-04-02 05:48:27 | tortor.dictum@Proineget.edu | 2015-01-27 03:12:10 |
157| 999970 | 229737 | Aaron Beasley | 1986-10-16 07:44:46 | lorem@nisi.net | 2015-03-01 06:38:21 |
158| 999971 | 789904 | Aaron Beasley | 1984-05-12 16:57:10 | dolor.Nulla.semper@rutrumeu.ca | 2016-02-24 00:15:19 |
159| 999972 | 773388 | Aaron Beard | 1986-03-25 03:32:17 | amet.risus.Donec@tristique.ca | 2015-12-07 12:53:11 |
160| 999973 | 171623 | Aaron Beard | 1992-07-29 18:52:29 | quam.elementum.at@Nullamfeugiatplacerat.edu | 2015-06-15 01:45:45 |
161| 999974 | 658446 | Aaron Bean | 1989-03-13 15:54:56 | est@eutempor.edu | 2015-06-07 07:52:42 |
162| 999975 | 407688 | Aaron Baxter | 1983-03-26 19:19:01 | sagittis@nisiCum.ca | 2015-09-03 20:55:30 |
163| 999976 | 924833 | Aaron Bauer | 1999-12-11 13:28:22 | Curabitur@nonenimMauris.edu | 2016-01-19 00:30:24 |
164| 999977 | 777753 | Aaron Battle | 1997-11-24 21:24:09 | id.mollis@ac.co.uk | 2015-05-08 00:17:21 |
165| 999978 | 217447 | Aaron Bass | 1992-12-27 15:59:17 | felis.purus.ac@urnajusto.co.uk | 2015-01-06 05:07:39 |
166| 999979 | 732683 | Aaron Bartlett | 1984-03-07 10:22:55 | Quisque.libero@etultrices.co.uk | 2016-02-08 13:03:43 |
167| 999980 | 158931 | Aaron Bartlett | 1997-07-23 00:43:30 | libero.Integer.in@risusDonec.ca | 2016-01-12 04:29:50 |
168+--------+--------+----------------+---------------------+------------------------------------------------+---------------------+
16920 rows in set (1 min 14.02 sec)
170-- catatan: kecepatannya hampir 2x cara biasa
171
172-- sekarang mari tambahkan index di kolom full_name
173CREATE INDEX `users_full_name` ON `users` (`full_name`);
174mysql> CREATE INDEX `users_full_name` ON `users` (`full_name`);
175Query OK, 0 rows affected (5.05 sec)
176Records: 0 Duplicates: 0 Warnings: 0
177
178-- cara biasa:
179+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
180| id | full_name | birth_date | email | register_date |
181+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
182| 711969 | Zorita Walls | 1989-11-29 07:11:47 | consequat.lectus.sit@Nuncullamcorper.org | 2015-07-31 07:42:27 |
183| 689376 | Zorita Walsh | 1990-10-17 22:55:56 | fermentum@loremac.net | 2015-08-29 07:58:20 |
184| 785577 | Zorita Walter | 1985-11-16 05:52:37 | consectetuer.mauris@sapienCras.com | 2016-02-22 07:00:58 |
185| 992994 | Zorita Walton | 1992-04-04 21:13:24 | In@sit.com | 2015-10-09 09:39:23 |
186| 139823 | Zorita Ware | 1993-06-25 05:16:48 | eu@tristique.net | 2015-07-27 03:34:34 |
187| 373327 | Zorita Ware | 1987-11-04 17:32:51 | amet@Phasellusdolorelit.ca | 2015-09-19 17:27:44 |
188| 789023 | Zorita Washington | 1998-10-14 01:55:58 | dictum@nuncullamcorper.com | 2015-05-03 14:48:36 |
189| 112559 | Zorita Washington | 1980-07-16 17:08:44 | adipiscing.elit@consequat.co.uk | 2015-02-25 23:11:41 |
190| 681705 | Zorita Webster | 1981-08-05 12:07:14 | convallis@blanditNamnulla.co.uk | 2015-05-30 15:02:23 |
191| 395543 | Zorita Weeks | 1982-08-20 04:23:21 | torquent.per.conubia@magnisdisparturient.net | 2015-12-13 07:40:03 |
192| 993066 | Zorita Weeks | 2000-05-03 15:43:28 | iaculis.aliquet.diam@interdumNuncsollicitudin.com | 2015-05-05 00:40:31 |
193| 181338 | Zorita Welch | 1995-01-21 09:01:06 | purus.in.molestie@dui.co.uk | 2015-09-24 22:26:52 |
194| 362332 | Zorita Welch | 2000-12-19 22:52:45 | gravida.mauris@velitAliquam.edu | 2015-10-15 11:35:38 |
195| 167832 | Zorita Welch | 1994-03-03 05:15:59 | semper.dui.lectus@mi.net | 2015-10-21 13:19:32 |
196| 501730 | Zorita Welch | 1998-05-28 19:29:57 | Sed.nulla.ante@aliquetnec.ca | 2015-08-04 14:03:28 |
197| 437806 | Zorita West | 1991-02-12 15:29:52 | enim@Suspendissecommodo.co.uk | 2015-05-07 00:46:19 |
198| 519870 | Zorita Whitaker | 1987-07-28 23:19:42 | eu.augue.porttitor@nonquamPellentesque.edu | 2015-11-19 03:56:00 |
199| 990047 | Zorita Whitehead | 1992-08-26 20:44:48 | nisl.Maecenas@hendrerit.org | 2015-05-18 23:34:23 |
200| 213451 | Zorita Whitehead | 2000-04-25 20:40:43 | est@loremsemperauctor.edu | 2015-08-12 06:08:08 |
201| 167061 | Zorita Whitney | 1984-09-10 13:42:37 | id@fringillacursus.ca | 2015-10-03 15:50:01 |
202+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
20320 rows in set (1 min 15.27 sec)
204-- hasil explain
205+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
206| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
207+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
208| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 100.00 | Using filesort |
209+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
210
211-- cara pos:
212+--------+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
213| pos | id | full_name | birth_date | email | register_date |
214+--------+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
215| 999961 | 711969 | Zorita Walls | 1989-11-29 07:11:47 | consequat.lectus.sit@Nuncullamcorper.org | 2015-07-31 07:42:27 |
216| 999962 | 689376 | Zorita Walsh | 1990-10-17 22:55:56 | fermentum@loremac.net | 2015-08-29 07:58:20 |
217| 999963 | 785577 | Zorita Walter | 1985-11-16 05:52:37 | consectetuer.mauris@sapienCras.com | 2016-02-22 07:00:58 |
218| 999964 | 992994 | Zorita Walton | 1992-04-04 21:13:24 | In@sit.com | 2015-10-09 09:39:23 |
219| 999965 | 139823 | Zorita Ware | 1993-06-25 05:16:48 | eu@tristique.net | 2015-07-27 03:34:34 |
220| 999966 | 373327 | Zorita Ware | 1987-11-04 17:32:51 | amet@Phasellusdolorelit.ca | 2015-09-19 17:27:44 |
221| 999967 | 789023 | Zorita Washington | 1998-10-14 01:55:58 | dictum@nuncullamcorper.com | 2015-05-03 14:48:36 |
222| 999968 | 112559 | Zorita Washington | 1980-07-16 17:08:44 | adipiscing.elit@consequat.co.uk | 2015-02-25 23:11:41 |
223| 999969 | 681705 | Zorita Webster | 1981-08-05 12:07:14 | convallis@blanditNamnulla.co.uk | 2015-05-30 15:02:23 |
224| 999970 | 395543 | Zorita Weeks | 1982-08-20 04:23:21 | torquent.per.conubia@magnisdisparturient.net | 2015-12-13 07:40:03 |
225| 999971 | 993066 | Zorita Weeks | 2000-05-03 15:43:28 | iaculis.aliquet.diam@interdumNuncsollicitudin.com | 2015-05-05 00:40:31 |
226| 999972 | 181338 | Zorita Welch | 1995-01-21 09:01:06 | purus.in.molestie@dui.co.uk | 2015-09-24 22:26:52 |
227| 999973 | 362332 | Zorita Welch | 2000-12-19 22:52:45 | gravida.mauris@velitAliquam.edu | 2015-10-15 11:35:38 |
228| 999974 | 167832 | Zorita Welch | 1994-03-03 05:15:59 | semper.dui.lectus@mi.net | 2015-10-21 13:19:32 |
229| 999975 | 501730 | Zorita Welch | 1998-05-28 19:29:57 | Sed.nulla.ante@aliquetnec.ca | 2015-08-04 14:03:28 |
230| 999976 | 437806 | Zorita West | 1991-02-12 15:29:52 | enim@Suspendissecommodo.co.uk | 2015-05-07 00:46:19 |
231| 999977 | 519870 | Zorita Whitaker | 1987-07-28 23:19:42 | eu.augue.porttitor@nonquamPellentesque.edu | 2015-11-19 03:56:00 |
232| 999978 | 990047 | Zorita Whitehead | 1992-08-26 20:44:48 | nisl.Maecenas@hendrerit.org | 2015-05-18 23:34:23 |
233| 999979 | 213451 | Zorita Whitehead | 2000-04-25 20:40:43 | est@loremsemperauctor.edu | 2015-08-12 06:08:08 |
234| 999980 | 167061 | Zorita Whitney | 1984-09-10 13:42:37 | id@fringillacursus.ca | 2015-10-03 15:50:01 |
235+--------+--------+-------------------+---------------------+---------------------------------------------------+---------------------+
23620 rows in set (53.28 sec)
237-- hasil explain
238+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
239| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
240+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
241| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 33.33 | Using where; Using filesort |
242| 2 | DERIVED | users | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 100.00 | Using filesort |
243+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
244
245-- catatan: wow, lebih dari 2 kali lipatnya
246
247-- desc
248-- cara biasa
249+--------+----------------+---------------------+------------------------------------------------+---------------------+
250| id | full_name | birth_date | email | register_date |
251+--------+----------------+---------------------+------------------------------------------------+---------------------+
252| 319574 | Aaron Berg | 1988-05-31 15:27:22 | ut@nullaIn.ca | 2015-01-23 15:21:05 |
253| 734226 | Aaron Benton | 1981-06-05 16:33:39 | consectetuer@euismodenim.co.uk | 2015-10-29 22:01:27 |
254| 673274 | Aaron Benson | 1995-07-22 04:58:13 | ullamcorper@Nunclectus.co.uk | 2016-01-01 14:50:53 |
255| 134255 | Aaron Bennett | 1989-08-22 13:31:34 | eu.eros@nectellusNunc.ca | 2015-09-15 13:28:26 |
256| 841412 | Aaron Bennett | 1983-04-12 10:56:38 | feugiat.Lorem.ipsum@Aliquameratvolutpat.edu | 2015-07-09 01:56:28 |
257| 64031 | Aaron Benjamin | 1981-04-20 16:24:33 | dictum.cursus.Nunc@adipiscingMaurismolestie.ca | 2015-06-13 02:07:37 |
258| 181292 | Aaron Benjamin | 1982-11-15 06:59:12 | Sed.nec.metus@malesuadavelvenenatis.ca | 2015-10-26 21:10:27 |
259| 840189 | Aaron Bender | 1997-09-22 01:39:04 | convallis.ante.lectus@etlaciniavitae.ca | 2015-10-05 22:42:04 |
260| 370860 | Aaron Becker | 1986-04-02 05:48:27 | tortor.dictum@Proineget.edu | 2015-01-27 03:12:10 |
261| 229737 | Aaron Beasley | 1986-10-16 07:44:46 | lorem@nisi.net | 2015-03-01 06:38:21 |
262| 789904 | Aaron Beasley | 1984-05-12 16:57:10 | dolor.Nulla.semper@rutrumeu.ca | 2016-02-24 00:15:19 |
263| 773388 | Aaron Beard | 1986-03-25 03:32:17 | amet.risus.Donec@tristique.ca | 2015-12-07 12:53:11 |
264| 171623 | Aaron Beard | 1992-07-29 18:52:29 | quam.elementum.at@Nullamfeugiatplacerat.edu | 2015-06-15 01:45:45 |
265| 658446 | Aaron Bean | 1989-03-13 15:54:56 | est@eutempor.edu | 2015-06-07 07:52:42 |
266| 407688 | Aaron Baxter | 1983-03-26 19:19:01 | sagittis@nisiCum.ca | 2015-09-03 20:55:30 |
267| 924833 | Aaron Bauer | 1999-12-11 13:28:22 | Curabitur@nonenimMauris.edu | 2016-01-19 00:30:24 |
268| 777753 | Aaron Battle | 1997-11-24 21:24:09 | id.mollis@ac.co.uk | 2015-05-08 00:17:21 |
269| 217447 | Aaron Bass | 1992-12-27 15:59:17 | felis.purus.ac@urnajusto.co.uk | 2015-01-06 05:07:39 |
270| 732683 | Aaron Bartlett | 1984-03-07 10:22:55 | Quisque.libero@etultrices.co.uk | 2016-02-08 13:03:43 |
271| 158931 | Aaron Bartlett | 1997-07-23 00:43:30 | libero.Integer.in@risusDonec.ca | 2016-01-12 04:29:50 |
272+--------+----------------+---------------------+------------------------------------------------+---------------------+
27320 rows in set (2 min 20.57 sec)
274-- hasil explain
275+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
276| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
277+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
278| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 100.00 | Using filesort |
279+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
280
281-- cara pos
282+--------+--------+----------------+---------------------+------------------------------------------------+---------------------+
283| pos | id | full_name | birth_date | email | register_date |
284+--------+--------+----------------+---------------------+------------------------------------------------+---------------------+
285| 999961 | 319574 | Aaron Berg | 1988-05-31 15:27:22 | ut@nullaIn.ca | 2015-01-23 15:21:05 |
286| 999962 | 734226 | Aaron Benton | 1981-06-05 16:33:39 | consectetuer@euismodenim.co.uk | 2015-10-29 22:01:27 |
287| 999963 | 673274 | Aaron Benson | 1995-07-22 04:58:13 | ullamcorper@Nunclectus.co.uk | 2016-01-01 14:50:53 |
288| 999964 | 134255 | Aaron Bennett | 1989-08-22 13:31:34 | eu.eros@nectellusNunc.ca | 2015-09-15 13:28:26 |
289| 999965 | 841412 | Aaron Bennett | 1983-04-12 10:56:38 | feugiat.Lorem.ipsum@Aliquameratvolutpat.edu | 2015-07-09 01:56:28 |
290| 999966 | 64031 | Aaron Benjamin | 1981-04-20 16:24:33 | dictum.cursus.Nunc@adipiscingMaurismolestie.ca | 2015-06-13 02:07:37 |
291| 999967 | 181292 | Aaron Benjamin | 1982-11-15 06:59:12 | Sed.nec.metus@malesuadavelvenenatis.ca | 2015-10-26 21:10:27 |
292| 999968 | 840189 | Aaron Bender | 1997-09-22 01:39:04 | convallis.ante.lectus@etlaciniavitae.ca | 2015-10-05 22:42:04 |
293| 999969 | 370860 | Aaron Becker | 1986-04-02 05:48:27 | tortor.dictum@Proineget.edu | 2015-01-27 03:12:10 |
294| 999970 | 229737 | Aaron Beasley | 1986-10-16 07:44:46 | lorem@nisi.net | 2015-03-01 06:38:21 |
295| 999971 | 789904 | Aaron Beasley | 1984-05-12 16:57:10 | dolor.Nulla.semper@rutrumeu.ca | 2016-02-24 00:15:19 |
296| 999972 | 773388 | Aaron Beard | 1986-03-25 03:32:17 | amet.risus.Donec@tristique.ca | 2015-12-07 12:53:11 |
297| 999973 | 171623 | Aaron Beard | 1992-07-29 18:52:29 | quam.elementum.at@Nullamfeugiatplacerat.edu | 2015-06-15 01:45:45 |
298| 999974 | 658446 | Aaron Bean | 1989-03-13 15:54:56 | est@eutempor.edu | 2015-06-07 07:52:42 |
299| 999975 | 407688 | Aaron Baxter | 1983-03-26 19:19:01 | sagittis@nisiCum.ca | 2015-09-03 20:55:30 |
300| 999976 | 924833 | Aaron Bauer | 1999-12-11 13:28:22 | Curabitur@nonenimMauris.edu | 2016-01-19 00:30:24 |
301| 999977 | 777753 | Aaron Battle | 1997-11-24 21:24:09 | id.mollis@ac.co.uk | 2015-05-08 00:17:21 |
302| 999978 | 217447 | Aaron Bass | 1992-12-27 15:59:17 | felis.purus.ac@urnajusto.co.uk | 2015-01-06 05:07:39 |
303| 999979 | 732683 | Aaron Bartlett | 1984-03-07 10:22:55 | Quisque.libero@etultrices.co.uk | 2016-02-08 13:03:43 |
304| 999980 | 158931 | Aaron Bartlett | 1997-07-23 00:43:30 | libero.Integer.in@risusDonec.ca | 2016-01-12 04:29:50 |
305+--------+--------+----------------+---------------------+------------------------------------------------+---------------------+
30620 rows in set (54.16 sec)
307-- hasil explain
308+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
309| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
310+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
311| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 33.33 | Using where; Using filesort |
312| 2 | DERIVED | users | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 100.00 | Using filesort |
313+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
314
315-- 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
316-- Cara biasa
317SELECT `id`, `full_name`, `birth_date`, `email`, `register_date` FROM `users` ORDER BY `full_name` ASC LIMIT 60, 20;
318-- hasil
319+--------+----------------+---------------------+---------------------------------------------+---------------------+
320| id | full_name | birth_date | email | register_date |
321+--------+----------------+---------------------+---------------------------------------------+---------------------+
322| 349431 | Aaron Branch | 1999-09-02 07:22:31 | sem.ut.cursus@quisdiamPellentesque.net | 2015-04-24 00:57:26 |
323| 939018 | Aaron Bray | 1991-07-11 22:53:15 | aliquet.Proin.velit@Curabiturvellectus.ca | 2015-03-14 00:51:30 |
324| 32163 | Aaron Brennan | 1981-07-11 20:24:29 | libero@torquentperconubia.ca | 2015-11-08 03:25:29 |
325| 502845 | Aaron Brennan | 1980-04-08 07:50:48 | ut.mi.Duis@dolorelitpellentesque.org | 2015-08-13 18:44:07 |
326| 556419 | Aaron Brennan | 1997-07-04 10:16:25 | pede@placerat.edu | 2015-11-29 20:20:01 |
327| 938914 | Aaron Brennan | 1982-01-29 12:45:45 | non.luctus@iaculisquis.edu | 2015-07-14 21:53:57 |
328| 163125 | Aaron Brewer | 1996-10-22 18:12:20 | ultrices.iaculis@velitjustonec.com | 2015-01-27 09:39:47 |
329| 819358 | Aaron Brewer | 1995-05-24 12:49:46 | dictum.eu@primis.net | 2015-10-26 02:44:15 |
330| 220648 | Aaron Britt | 1985-02-23 13:00:06 | leo@massaQuisque.org | 2015-08-20 02:56:57 |
331| 872558 | Aaron Brock | 1984-09-30 04:49:56 | interdum.feugiat@venenatis.com | 2015-04-14 11:47:32 |
332| 447693 | Aaron Brown | 1985-08-28 02:04:12 | montes.nascetur@eratVivamusnisi.net | 2015-06-19 05:35:39 |
333| 79683 | Aaron Browning | 1980-11-12 15:59:30 | ornare@inaliquet.co.uk | 2015-01-16 23:42:54 |
334| 347786 | Aaron Browning | 2000-09-24 05:10:37 | lobortis.quam.a@Vestibulumaccumsanneque.net | 2016-02-08 14:32:22 |
335| 417605 | Aaron Browning | 1995-12-22 08:48:03 | sem.Pellentesque@euodioPhasellus.net | 2015-09-01 16:41:20 |
336| 883563 | Aaron Browning | 1998-09-21 17:14:16 | eleifend@arcuVestibulum.ca | 2015-08-02 11:51:52 |
337| 511872 | Aaron Bruce | 1999-07-10 02:32:11 | In.at.pede@imperdiet.co.uk | 2015-06-13 02:17:22 |
338| 526648 | Aaron Buckley | 1982-09-10 19:13:35 | nonummy@dictumauguemalesuada.com | 2016-02-26 16:18:38 |
339| 382172 | Aaron Bullock | 1987-08-26 14:36:14 | sagittis.augue.eu@nec.org | 2015-12-22 15:49:50 |
340| 799606 | Aaron Bullock | 1995-08-01 07:57:22 | libero.lacus@nec.co.uk | 2016-02-22 00:14:35 |
341| 690515 | Aaron Burch | 2000-09-03 20:28:23 | sodales.Mauris@molestiepharetra.org | 2015-04-05 23:08:28 |
342+--------+----------------+---------------------+---------------------------------------------+---------------------+
34320 rows in set (0.03 sec)
344-- explain
345+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+
346| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
347+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+
348| 1 | SIMPLE | users | NULL | index | NULL | users_full_name | 258 | NULL | 80 | 100.00 | NULL |
349+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+
350
351-- cara pos
352SET @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;
353-- hasil
354+------+--------+----------------+---------------------+---------------------------------------------+---------------------+
355| pos | id | full_name | birth_date | email | register_date |
356+------+--------+----------------+---------------------+---------------------------------------------+---------------------+
357| 61 | 349431 | Aaron Branch | 1999-09-02 07:22:31 | sem.ut.cursus@quisdiamPellentesque.net | 2015-04-24 00:57:26 |
358| 62 | 939018 | Aaron Bray | 1991-07-11 22:53:15 | aliquet.Proin.velit@Curabiturvellectus.ca | 2015-03-14 00:51:30 |
359| 63 | 502845 | Aaron Brennan | 1980-04-08 07:50:48 | ut.mi.Duis@dolorelitpellentesque.org | 2015-08-13 18:44:07 |
360| 64 | 556419 | Aaron Brennan | 1997-07-04 10:16:25 | pede@placerat.edu | 2015-11-29 20:20:01 |
361| 65 | 938914 | Aaron Brennan | 1982-01-29 12:45:45 | non.luctus@iaculisquis.edu | 2015-07-14 21:53:57 |
362| 66 | 32163 | Aaron Brennan | 1981-07-11 20:24:29 | libero@torquentperconubia.ca | 2015-11-08 03:25:29 |
363| 67 | 163125 | Aaron Brewer | 1996-10-22 18:12:20 | ultrices.iaculis@velitjustonec.com | 2015-01-27 09:39:47 |
364| 68 | 819358 | Aaron Brewer | 1995-05-24 12:49:46 | dictum.eu@primis.net | 2015-10-26 02:44:15 |
365| 69 | 220648 | Aaron Britt | 1985-02-23 13:00:06 | leo@massaQuisque.org | 2015-08-20 02:56:57 |
366| 70 | 872558 | Aaron Brock | 1984-09-30 04:49:56 | interdum.feugiat@venenatis.com | 2015-04-14 11:47:32 |
367| 71 | 447693 | Aaron Brown | 1985-08-28 02:04:12 | montes.nascetur@eratVivamusnisi.net | 2015-06-19 05:35:39 |
368| 72 | 79683 | Aaron Browning | 1980-11-12 15:59:30 | ornare@inaliquet.co.uk | 2015-01-16 23:42:54 |
369| 73 | 417605 | Aaron Browning | 1995-12-22 08:48:03 | sem.Pellentesque@euodioPhasellus.net | 2015-09-01 16:41:20 |
370| 74 | 883563 | Aaron Browning | 1998-09-21 17:14:16 | eleifend@arcuVestibulum.ca | 2015-08-02 11:51:52 |
371| 75 | 347786 | Aaron Browning | 2000-09-24 05:10:37 | lobortis.quam.a@Vestibulumaccumsanneque.net | 2016-02-08 14:32:22 |
372| 76 | 511872 | Aaron Bruce | 1999-07-10 02:32:11 | In.at.pede@imperdiet.co.uk | 2015-06-13 02:17:22 |
373| 77 | 526648 | Aaron Buckley | 1982-09-10 19:13:35 | nonummy@dictumauguemalesuada.com | 2016-02-26 16:18:38 |
374| 78 | 799606 | Aaron Bullock | 1995-08-01 07:57:22 | libero.lacus@nec.co.uk | 2016-02-22 00:14:35 |
375| 79 | 382172 | Aaron Bullock | 1987-08-26 14:36:14 | sagittis.augue.eu@nec.org | 2015-12-22 15:49:50 |
376| 80 | 690515 | Aaron Burch | 2000-09-03 20:28:23 | sodales.Mauris@molestiepharetra.org | 2015-04-05 23:08:28 |
377+------+--------+----------------+---------------------+---------------------------------------------+---------------------+
37820 rows in set (2 min 27.26 sec)
379-- explain
380+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
381| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
382+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
383| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 33.33 | Using where; Using filesort |
384| 2 | DERIVED | users | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 100.00 | Using filesort |
385+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
386
387-- pakai cara pos, mau melihat halaman awal atau akhir, waktu yang dibutuhkan hampir sama.
388-- 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