-- 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
