-- ini benchmark ulang, dengan menghilangkan order by di outer query, dan melakukan select thd nama kolom ketimbang select * -- data hasil query tidak ditampilkan, karena sama saja dengan data sebelumnya. Fokus hanya pada waktu query. -- kolom full_name sudah dijadikan index. -- data sejumlah 1juta row, per halaman 20 data, berarti total ada 50ribu data -- sekarang coba kita ambil data di halaman 49.999 sebanyak 20 data -- offset = ( `49999 - 1 ) * 20 = 999960 -- cara biasa, ambil data urut full_name SELECT `id`, `full_name`, `birth_date`, `email`, `register_date` FROM `users` ORDER BY `full_name` ASC LIMIT 999960, 20; -- hasil 20 rows in set (2 min 19.67 sec) -- explain +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+ -- pakai subquery SET @row_number=0; SELECT `pos`, `id`, `full_name`, `birth_date`, `email`, `register_date` FROM (SELECT @row_number:=@row_number+1 AS `pos`, `id`, `full_name`, `birth_date`, `email`, `register_date` FROM `users` ORDER BY `full_name` ASC) `users_with_pos` WHERE `pos` > 999960 LIMIT 20; -- hasil 20 rows in set (2 min 26.87 sec) [select *] 20 rows in set (1 min 36.70 sec) -- explain +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 33.33 | Using where | | 2 | DERIVED | users | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 100.00 | Using filesort | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+ -- desc SELECT `id`, `full_name`, `birth_date`, `email`, `register_date` FROM `users` ORDER BY `full_name` DESC LIMIT 999960, 20; -- hasil 20 rows in set (2 min 37.97 sec) -- explain +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+ -- pakai subquery SET @row_number=0; SELECT `pos`, `id`, `full_name`, `birth_date`, `email`, `register_date` FROM (SELECT @row_number:=@row_number+1 AS `pos`, `id`, `full_name`, `birth_date`, `email`, `register_date` FROM `users` ORDER BY `full_name` DESC) `users_with_pos` WHERE `pos` > 999960 LIMIT 20; -- hasil 20 rows in set (2 min 41.67 sec) [select *] 20 rows in set (2 min 28.55 sec) -- explain +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 33.33 | Using where | | 2 | DERIVED | users | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 100.00 | Using filesort | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+ -- halaman 4, offset 60, limit 20 SELECT `id`, `full_name`, `birth_date`, `email`, `register_date` FROM `users` ORDER BY `full_name` ASC LIMIT 60, 20; -- hasil 20 rows in set (0.07 sec) -- explain +----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+ | 1 | SIMPLE | users | NULL | index | NULL | users_full_name | 258 | NULL | 80 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+ -- pakai subquery SET @row_number=0; SELECT `pos`, `id`, `full_name`, `birth_date`, `email`, `register_date` FROM (SELECT @row_number:=@row_number+1 AS `pos`, `id`, `full_name`, `birth_date`, `email`, `register_date` FROM `users` ORDER BY `full_name` ASC) `users_with_pos` WHERE `pos` > 60 LIMIT 20; -- hasil 20 rows in set (2 min 35.46 sec) [select *] 20 rows in set (1 min 53.38 sec) -- explain +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 33.33 | Using where | | 2 | DERIVED | users | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 100.00 | Using filesort | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+ -- desc SELECT `id`, `full_name`, `birth_date`, `email`, `register_date` FROM `users` ORDER BY `full_name` DESC LIMIT 60, 20; -- hasil 20 rows in set (0.20 sec) -- explain +----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+ | 1 | SIMPLE | users | NULL | index | NULL | users_full_name | 258 | NULL | 80 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+ -- pakai subquery SET @row_number=0; SELECT `pos`, `id`, `full_name`, `birth_date`, `email`, `register_date` FROM (SELECT @row_number:=@row_number+1 AS `pos`, `id`, `full_name`, `birth_date`, `email`, `register_date` FROM `users` ORDER BY `full_name` DESC) `users_with_pos` WHERE `pos` > 60 LIMIT 20; -- hasil 20 rows in set (2 min 37.37 sec) [select *] 20 rows in set (1 min 1.63 sec) -- explain +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 33.33 | Using where | | 2 | DERIVED | users | NULL | ALL | NULL | NULL | NULL | NULL | 993808 | 100.00 | Using filesort | +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
Comments