Skip to content

Instantly share code, notes, and snippets.

@mdcallag

mdcallag/gist:ce7575d9ea578390d5c1a87679039b77

Last active November 8, 2020 19:42
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mdcallag/ce7575d9ea578390d5c1a87679039b77 to your computer and use it in GitHub Desktop.
Save mdcallag/ce7575d9ea578390d5c1a87679039b77 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
--------------
drop table if exists ten
--------------
Query OK, 0 rows affected (0.03 sec)
--------------
drop table if exists one_k
--------------
Query OK, 0 rows affected (0.01 sec)
--------------
drop table if exists tq
--------------
Query OK, 0 rows affected (0.02 sec)
--------------
create table ten(a int primary key)
--------------
Query OK, 0 rows affected (0.03 sec)
--------------
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)
--------------
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
--------------
create table one_k(a int primary key)
--------------
Query OK, 0 rows affected (0.03 sec)
--------------
insert into one_k select a.a + b.a* 10 + c.a * 100 from ten a, ten b, ten c
--------------
Query OK, 1000 rows affected (0.01 sec)
Records: 1000 Duplicates: 0 Warnings: 0
--------------
create table tq (
pk int primary key auto_increment,
j int,
k int
)
--------------
Query OK, 0 rows affected (0.03 sec)
--------------
create index jxa on tq(j)
--------------
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
--------------
insert into tq (j, k)
select
grp_no.a,
123456
from
(select (A.a+1000*B.a) from one_k A, ten B) as grp_contents,
one_k as grp_no
--------------
Query OK, 10000000 rows affected (1 min 31.06 sec)
Records: 10000000 Duplicates: 0 Warnings: 0
--------------
analyze table tq
--------------
+----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------+---------+----------+----------+
| test2.tq | analyze | status | OK |
+----------+---------+----------+----------+
1 row in set (0.02 sec)
--------------
explain format=tree
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
--------------
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on t1 using <auto_key0> (myrank=1)
-> Materialize CTE t1
-> Window aggregate: rank() OVER (PARTITION BY tq.j ORDER BY tq.pk desc )
-> Sort: tq.j, tq.pk DESC (cost=1003180.26 rows=9979810)
-> Table scan on tq (cost=1003180.26 rows=9979810)
1 row in set (0.00 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 |
+----------+------+--------+
| 8542459 | 0 | 123456 |
| 8543918 | 1 | 123456 |
| 8545377 | 2 | 123456 |
| 8546836 | 3 | 123456 |
| 8548295 | 4 | 123456 |
...
| 10000000 | 999 | 123456 |
+----------+------+--------+
1000 rows in set (19.02 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 | 1000 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 10000001 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 10000000 |
+----------------------------+----------+
18 rows in set (0.02 sec)
--------------
show status like '%sort%'
--------------
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| Sort_merge_passes | 254 |
| Sort_range | 0 |
| Sort_rows | 10000000 |
| Sort_scan | 1 |
+-------------------+----------+
4 rows in set (0.00 sec)
--------------
alter table tq drop index jxa
--------------
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
--------------
create index jx2 on tq(j, pk)
--------------
Query OK, 0 rows affected (19.57 sec)
Records: 0 Duplicates: 0 Warnings: 0
--------------
analyze table tq
--------------
+----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------+---------+----------+----------+
| test2.tq | analyze | status | OK |
+----------+---------+----------+----------+
1 row in set (0.03 sec)
--------------
explain format=tree
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
--------------
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on t1 using <auto_key0> (myrank=1)
-> Materialize CTE t1
-> Window aggregate: rank() OVER (PARTITION BY tq.j ORDER BY tq.pk desc )
-> Sort: tq.j, tq.pk DESC (cost=1003180.26 rows=9979810)
-> Table scan on tq (cost=1003180.26 rows=9979810)
1 row in set (0.00 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 |
+----------+------+--------+
| 8542459 | 0 | 123456 |
| 8543918 | 1 | 123456 |
| 8545377 | 2 | 123456 |
| 8546836 | 3 | 123456 |
| 8548295 | 4 | 123456 |
...
| 10000000 | 999 | 123456 |
+----------+------+--------+
1000 rows in set (18.90 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 | 1000 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 10000001 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 10000000 |
+----------------------------+----------+
18 rows in set (0.00 sec)
--------------
show status like '%sort%'
--------------
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| Sort_merge_passes | 254 |
| Sort_range | 0 |
| Sort_rows | 10000000 |
| Sort_scan | 1 |
+-------------------+----------+
4 rows in set (0.01 sec)
--------------
alter table tq drop index jx2
--------------
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
--------------
create index jxd2 on tq(j, pk DESC)
--------------
Query OK, 0 rows affected (18.76 sec)
Records: 0 Duplicates: 0 Warnings: 0
--------------
analyze table tq
--------------
+----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------+---------+----------+----------+
| test2.tq | analyze | status | OK |
+----------+---------+----------+----------+
1 row in set (0.02 sec)
--------------
explain format=tree
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
--------------
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on t1 using <auto_key0> (myrank=1)
-> Materialize CTE t1
-> Window aggregate: rank() OVER (PARTITION BY tq.j ORDER BY tq.pk desc )
-> Sort: tq.j, tq.pk DESC (cost=1003180.26 rows=9979810)
-> Table scan on tq (cost=1003180.26 rows=9979810)
1 row in set (0.00 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 |
+----------+------+--------+
| 8542459 | 0 | 123456 |
| 8543918 | 1 | 123456 |
| 8545377 | 2 | 123456 |
| 8546836 | 3 | 123456 |
| 8548295 | 4 | 123456 |
...
| 10000000 | 999 | 123456 |
+----------+------+--------+
1000 rows in set (18.97 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 | 1000 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 10000001 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 10000000 |
+----------------------------+----------+
18 rows in set (0.00 sec)
--------------
show status like '%sort%'
--------------
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| Sort_merge_passes | 254 |
| Sort_range | 0 |
| Sort_rows | 10000000 |
| Sort_scan | 1 |
+-------------------+----------+
4 rows in set (0.00 sec)
--------------
explain format=tree
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
--------------
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join
-> Filter: (t2.maxpk is not null)
-> Table scan on t2 (cost=103.86 rows=901)
-> Materialize
-> Group aggregate (computed in earlier step): max(tq.pk)
-> Index range scan on tq using index_for_group_by(jxd2) (cost=1216.35 rows=901)
-> Single-row index lookup on t1 using PRIMARY (pk=t2.maxpk) (cost=0.25 rows=1)
1 row 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 |
+----------+------+--------+
| 8542459 | 0 | 123456 |
| 8543918 | 1 | 123456 |
| 8545377 | 2 | 123456 |
| 8546836 | 3 | 123456 |
| 8548295 | 4 | 123456 |
...
| 10000000 | 999 | 123456 |
+----------+------+--------+
1000 rows in set (0.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 | 2002 |
| Handler_read_last | 1 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 1001 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 1000 |
+----------------------------+-------+
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)
Bye
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment