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