API tools faq
paste
donnykurnia

Hasil benchmark ulang

donnykurnia
Mar 12th, 2016
280
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 9.52 KB | None | 0 0
raw download clone embed print report
  1. -- ini benchmark ulang, dengan menghilangkan order by di outer query, dan melakukan select thd nama kolom ketimbang select *
  2. -- data hasil query tidak ditampilkan, karena sama saja dengan data sebelumnya. Fokus hanya pada waktu query.
  3. -- kolom full_name sudah dijadikan index.
  4.  
  5. -- data sejumlah 1juta row, per halaman 20 data, berarti total ada 50ribu data
  6. -- sekarang coba kita ambil data di halaman 49.999 sebanyak 20 data
  7. -- offset = ( `49999 - 1 ) * 20 = 999960
  8.  
  9. -- cara biasa, ambil data urut full_name
  10. SELECT `id`,
  11.        `full_name`,
  12.        `birth_date`,
  13.        `email`,
  14.        `register_date`
  15. FROM `users`
  16. ORDER BY `full_name` ASC LIMIT 999960, 20;
  17. -- hasil
  18. 20 rows in set (2 min 19.67 sec)
  19. -- explain
  20. +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  21. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
  22. +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  23. |  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |   100.00 | Using filesort |
  24. +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  25. -- pakai subquery
  26.  
  27. SET @row_number=0;
  28. SELECT `pos`,
  29.        `id`,
  30.        `full_name`,
  31.        `birth_date`,
  32.        `email`,
  33.        `register_date`
  34. FROM
  35.   (SELECT @row_number:=@row_number+1 AS `pos`, `id`,
  36.                                                `full_name`,
  37.                                                `birth_date`,
  38.                                                `email`,
  39.                                                `register_date`
  40.    FROM `users`
  41.    ORDER BY `full_name` ASC) `users_with_pos`
  42. WHERE `pos` > 999960 LIMIT 20;
  43.  
  44. -- hasil
  45. 20 rows in set (2 min 26.87 sec) [select *]
  46. 20 rows in set (1 min 36.70 sec)
  47. -- explain
  48. +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  49. | id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
  50. +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  51. |  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |    33.33 | Using where    |
  52. |  2 | DERIVED     | users      | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |   100.00 | Using filesort |
  53. +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  54.  
  55. -- desc
  56. SELECT `id`,
  57.        `full_name`,
  58.        `birth_date`,
  59.        `email`,
  60.        `register_date`
  61. FROM `users`
  62. ORDER BY `full_name` DESC LIMIT 999960, 20;
  63.  
  64. -- hasil
  65. 20 rows in set (2 min 37.97 sec)
  66. -- explain
  67. +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  68. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
  69. +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  70. |  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |   100.00 | Using filesort |
  71. +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  72. -- pakai subquery
  73.  
  74. SET @row_number=0;
  75. SELECT `pos`,
  76.        `id`,
  77.        `full_name`,
  78.        `birth_date`,
  79.        `email`,
  80.        `register_date`
  81. FROM
  82.   (SELECT @row_number:=@row_number+1 AS `pos`, `id`,
  83.                                                `full_name`,
  84.                                                `birth_date`,
  85.                                                `email`,
  86.                                                `register_date`
  87.    FROM `users`
  88.    ORDER BY `full_name` DESC) `users_with_pos`
  89. WHERE `pos` > 999960 LIMIT 20;
  90.  
  91. -- hasil
  92. 20 rows in set (2 min 41.67 sec) [select *]
  93. 20 rows in set (2 min 28.55 sec)
  94. -- explain
  95. +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  96. | id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
  97. +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  98. |  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |    33.33 | Using where    |
  99. |  2 | DERIVED     | users      | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |   100.00 | Using filesort |
  100. +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  101.  
  102. -- halaman 4, offset 60, limit 20
  103. SELECT `id`,
  104.        `full_name`,
  105.        `birth_date`,
  106.        `email`,
  107.        `register_date`
  108. FROM `users`
  109. ORDER BY `full_name` ASC LIMIT 60, 20;
  110.  
  111. -- hasil
  112. 20 rows in set (0.07 sec)
  113. -- explain
  114. +----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+
  115. | id | select_type | table | partitions | type  | possible_keys | key             | key_len | ref  | rows | filtered | Extra |
  116. +----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+
  117. |  1 | SIMPLE      | users | NULL       | index | NULL          | users_full_name | 258     | NULL |   80 |   100.00 | NULL  |
  118. +----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+
  119. -- pakai subquery
  120.  
  121. SET @row_number=0;
  122. SELECT `pos`,
  123.        `id`,
  124.        `full_name`,
  125.        `birth_date`,
  126.        `email`,
  127.        `register_date`
  128. FROM
  129.   (SELECT @row_number:=@row_number+1 AS `pos`, `id`,
  130.                                                `full_name`,
  131.                                                `birth_date`,
  132.                                                `email`,
  133.                                                `register_date`
  134.    FROM `users`
  135.    ORDER BY `full_name` ASC) `users_with_pos`
  136. WHERE `pos` > 60 LIMIT 20;
  137.  
  138. -- hasil
  139. 20 rows in set (2 min 35.46 sec) [select *]
  140. 20 rows in set (1 min 53.38 sec)
  141. -- explain
  142. +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  143. | id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
  144. +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  145. |  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |    33.33 | Using where    |
  146. |  2 | DERIVED     | users      | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |   100.00 | Using filesort |
  147. +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  148.  
  149. -- desc
  150. SELECT `id`,
  151.        `full_name`,
  152.        `birth_date`,
  153.        `email`,
  154.        `register_date`
  155. FROM `users`
  156. ORDER BY `full_name` DESC LIMIT 60, 20;
  157.  
  158. -- hasil
  159. 20 rows in set (0.20 sec)
  160. -- explain
  161. +----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+
  162. | id | select_type | table | partitions | type  | possible_keys | key             | key_len | ref  | rows | filtered | Extra |
  163. +----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+
  164. |  1 | SIMPLE      | users | NULL       | index | NULL          | users_full_name | 258     | NULL |   80 |   100.00 | NULL  |
  165. +----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-------+
  166. -- pakai subquery
  167.  
  168. SET @row_number=0;
  169. SELECT `pos`,
  170.        `id`,
  171.        `full_name`,
  172.        `birth_date`,
  173.        `email`,
  174.        `register_date`
  175. FROM
  176.   (SELECT @row_number:=@row_number+1 AS `pos`, `id`,
  177.                                                `full_name`,
  178.                                                `birth_date`,
  179.                                                `email`,
  180.                                                `register_date`
  181.    FROM `users`
  182.    ORDER BY `full_name` DESC) `users_with_pos`
  183. WHERE `pos` > 60 LIMIT 20;
  184.  
  185. -- hasil
  186. 20 rows in set (2 min 37.37 sec) [select *]
  187. 20 rows in set (1 min 1.63 sec)
  188. -- explain
  189. +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  190. | id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
  191. +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
  192. |  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |    33.33 | Using where    |
  193. |  2 | DERIVED     | users      | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 993808 |   100.00 | Using filesort |
  194. +----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!