Skip to content

Instantly share code, notes, and snippets.

@mdcallag

mdcallag/gist:3c40a6cc8a673469e94b28d2c55027d2

Created November 8, 2020 19:28
Show Gist options
  • Save mdcallag/3c40a6cc8a673469e94b28d2c55027d2 to your computer and use it in GitHub Desktop.
Save mdcallag/3c40a6cc8a673469e94b28d2c55027d2 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
--------------
set long_query_time=0
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
flush status
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
SELECT max(pk), j FROM tq GROUP BY j
--------------
+---------+----+
| max(pk) | j |
+---------+----+
| 8192 | 1 |
| 24575 | 2 |
| 40958 | 3 |
| 57341 | 4 |
| 73724 | 5 |
| 90107 | 6 |
| 106490 | 7 |
| 122873 | 8 |
| 139256 | 9 |
| 155639 | 10 |
+---------+----+
10 rows in set (0.00 sec)
--------------
show status like 'Handler%'
--------------
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 12 |
| Handler_read_last | 1 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)
--------------
show status like '%sort%'
--------------
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
+-------------------+-------+
4 rows in set (0.00 sec)
--------------
flush status
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
SELECT t1.pk, t1.j, t1.k
FROM tq t1, (SELECT max(pk) as maxpk, j FROM tq GROUP BY j) t2
WHERE t2.maxpk = t1.pk
--------------
+--------+----+----+
| pk | j | k |
+--------+----+----+
| 8192 | 1 | 92 |
| 24575 | 2 | 75 |
| 40958 | 3 | 58 |
| 57341 | 4 | 41 |
| 73724 | 5 | 24 |
| 90107 | 6 | 7 |
| 106490 | 7 | 90 |
| 122873 | 8 | 73 |
| 139256 | 9 | 56 |
| 155639 | 10 | 39 |
+--------+----+----+
10 rows in set (0.00 sec)
--------------
show status like 'Handler%'
--------------
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 4 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 22 |
| Handler_read_last | 1 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 11 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 10 |
+----------------------------+-------+
18 rows in set (0.00 sec)
--------------
show status like '%sort%'
--------------
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
+-------------------+-------+
4 rows in set (0.00 sec)
--------------
flush status
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
SELECT pk, j, k FROM tq t1
WHERE pk=(SELECT MAX(t2.pk) FROM tq t2 WHERE t1.j = t2.j)
--------------
+--------+----+----+
| pk | j | k |
+--------+----+----+
| 8192 | 1 | 92 |
| 24575 | 2 | 75 |
| 40958 | 3 | 58 |
| 57341 | 4 | 41 |
| 73724 | 5 | 24 |
| 90107 | 6 | 7 |
| 106490 | 7 | 90 |
| 122873 | 8 | 73 |
| 139256 | 9 | 56 |
| 155639 | 10 | 39 |
+--------+----+----+
10 rows in set (2 min 13.59 sec)
--------------
show status like 'Handler%'
--------------
+----------------------------+-----------+
| Variable_name | Value |
+----------------------------+-----------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 4 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 81921 |
| Handler_read_last | 0 |
| Handler_read_next | 671088640 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 81921 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-----------+
18 rows in set (0.00 sec)
--------------
show status like '%sort%'
--------------
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
+-------------------+-------+
4 rows in set (0.00 sec)
--------------
flush status
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
SELECT t1.pk, t1.j, t1.k FROM tq t1
LEFT JOIN tq t2 ON t1.j = t2.j AND t1.pk < t2.pk
WHERE t2.j IS NULL
--------------
+--------+----+----+
| pk | j | k |
+--------+----+----+
| 8192 | 1 | 92 |
| 24575 | 2 | 75 |
| 40958 | 3 | 58 |
| 57341 | 4 | 41 |
| 73724 | 5 | 24 |
| 90107 | 6 | 7 |
| 106490 | 7 | 90 |
| 122873 | 8 | 73 |
| 139256 | 9 | 56 |
| 155639 | 10 | 39 |
+--------+----+----+
10 rows in set (1.04 sec)
--------------
show status like 'Handler%'
--------------
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 4 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 81921 |
| Handler_read_last | 0 |
| Handler_read_next | 81920 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 81921 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)
--------------
show status like '%sort%'
--------------
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
+-------------------+-------+
4 rows in set (0.01 sec)
--------------
flush status
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
WITH t1 AS (SELECT pk, j, k,
RANK() OVER (PARTITION by j ORDER BY pk DESC) AS myrank FROM tq)
SELECT pk, j, k from t1 WHERE myrank=1
--------------
+--------+----+----+
| pk | j | k |
+--------+----+----+
| 8192 | 1 | 92 |
| 24575 | 2 | 75 |
| 40958 | 3 | 58 |
| 57341 | 4 | 41 |
| 73724 | 5 | 24 |
| 90107 | 6 | 7 |
| 106490 | 7 | 90 |
| 122873 | 8 | 73 |
| 139256 | 9 | 56 |
| 155639 | 10 | 39 |
+--------+----+----+
10 rows in set (0.10 sec)
--------------
show status like 'Handler%'
--------------
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 2 |
| Handler_read_last | 0 |
| Handler_read_next | 10 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 81921 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 81920 |
+----------------------------+-------+
18 rows in set (0.00 sec)
--------------
show status like '%sort%'
--------------
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 1 |
| Sort_range | 0 |
| Sort_rows | 81920 |
| Sort_scan | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)
--------------
flush status
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
WITH t1 AS (SELECT /*+ INDEX(tq x) */ pk, j, k,
RANK() OVER (PARTITION by j ORDER BY pk DESC) AS myrank FROM tq)
SELECT /*+ INDEX(tq x) */ pk, j, k from t1 WHERE myrank=1
--------------
+--------+----+----+
| pk | j | k |
+--------+----+----+
| 8192 | 1 | 92 |
| 24575 | 2 | 75 |
| 40958 | 3 | 58 |
| 57341 | 4 | 41 |
| 73724 | 5 | 24 |
| 90107 | 6 | 7 |
| 106490 | 7 | 90 |
| 122873 | 8 | 73 |
| 139256 | 9 | 56 |
| 155639 | 10 | 39 |
+--------+----+----+
10 rows in set, 2 warnings (0.10 sec)
--------------
show status like 'Handler%'
--------------
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 2 |
| Handler_read_last | 0 |
| Handler_read_next | 10 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 81921 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 81920 |
+----------------------------+-------+
18 rows in set (0.00 sec)
--------------
show status like '%sort%'
--------------
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 1 |
| Sort_range | 0 |
| Sort_rows | 81920 |
| Sort_scan | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)
Bye
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment