Created
November 6, 2020 21:05
-
-
Save mdcallag/4ebb8c7471dc0c222e8ee402431d6fa8 to your computer and use it in GitHub Desktop.
output for setup
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -------------- | |
| drop table if exists ti | |
| -------------- | |
| Query OK, 0 rows affected (0.06 sec) | |
| -------------- | |
| drop table if exists tq | |
| -------------- | |
| Query OK, 0 rows affected (0.07 sec) | |
| -------------- | |
| create table ti(i int) | |
| -------------- | |
| Query OK, 0 rows affected (0.13 sec) | |
| -------------- | |
| create table tq(pk int primary key auto_increment, j int NOT NULL, k int NOT NULL) | |
| -------------- | |
| Query OK, 0 rows affected (0.13 sec) | |
| -------------- | |
| insert into ti values (null), (null), (null), (null) | |
| -------------- | |
| Query OK, 4 rows affected (0.00 sec) | |
| Records: 4 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| insert into ti select null from ti | |
| -------------- | |
| Query OK, 4 rows affected (0.01 sec) | |
| Records: 4 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| insert into ti select null from ti | |
| -------------- | |
| Query OK, 8 rows affected (0.00 sec) | |
| Records: 8 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| insert into ti select null from ti | |
| -------------- | |
| Query OK, 16 rows affected (0.00 sec) | |
| Records: 16 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| insert into ti select null from ti | |
| -------------- | |
| Query OK, 32 rows affected (0.00 sec) | |
| Records: 32 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| insert into ti select null from ti | |
| -------------- | |
| Query OK, 64 rows affected (0.00 sec) | |
| Records: 64 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| insert into ti select null from ti | |
| -------------- | |
| Query OK, 128 rows affected (0.01 sec) | |
| Records: 128 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| insert into ti select null from ti | |
| -------------- | |
| Query OK, 256 rows affected (0.00 sec) | |
| Records: 256 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| insert into ti select null from ti | |
| -------------- | |
| Query OK, 512 rows affected (0.08 sec) | |
| Records: 512 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| insert into ti select null from ti | |
| -------------- | |
| Query OK, 1024 rows affected (0.04 sec) | |
| Records: 1024 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| insert into ti select null from ti | |
| -------------- | |
| Query OK, 2048 rows affected (0.15 sec) | |
| Records: 2048 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| insert into ti select null from ti | |
| -------------- | |
| Query OK, 4096 rows affected (0.33 sec) | |
| Records: 4096 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| insert into tq (pk,j,k) select null, 1, 1 from ti | |
| -------------- | |
| Query OK, 8192 rows affected (0.71 sec) | |
| Records: 8192 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| update tq set k = mod(pk, 100) where j = 1 | |
| -------------- | |
| Query OK, 8110 rows affected (0.13 sec) | |
| Rows matched: 8192 Changed: 8110 Warnings: 0 | |
| -------------- | |
| insert into tq (pk,j,k) select null, 2, 2 from ti | |
| -------------- | |
| Query OK, 8192 rows affected (0.94 sec) | |
| Records: 8192 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| update tq set k = mod(pk, 100) where j = 2 | |
| -------------- | |
| Query OK, 8110 rows affected (0.05 sec) | |
| Rows matched: 8192 Changed: 8110 Warnings: 0 | |
| -------------- | |
| insert into tq (pk,j,k) select null, 3, 3 from ti | |
| -------------- | |
| Query OK, 8192 rows affected (0.06 sec) | |
| Records: 8192 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| update tq set k = mod(pk, 100) where j = 3 | |
| -------------- | |
| Query OK, 8110 rows affected (0.06 sec) | |
| Rows matched: 8192 Changed: 8110 Warnings: 0 | |
| -------------- | |
| insert into tq (pk,j,k) select null, 4, 4 from ti | |
| -------------- | |
| Query OK, 8192 rows affected (0.05 sec) | |
| Records: 8192 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| update tq set k = mod(pk, 100) where j = 4 | |
| -------------- | |
| Query OK, 8110 rows affected (0.08 sec) | |
| Rows matched: 8192 Changed: 8110 Warnings: 0 | |
| -------------- | |
| insert into tq (pk,j,k) select null, 5, 5 from ti | |
| -------------- | |
| Query OK, 8192 rows affected (0.05 sec) | |
| Records: 8192 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| update tq set k = mod(pk, 100) where j = 5 | |
| -------------- | |
| Query OK, 8110 rows affected (0.08 sec) | |
| Rows matched: 8192 Changed: 8110 Warnings: 0 | |
| -------------- | |
| insert into tq (pk,j,k) select null, 6, 6 from ti | |
| -------------- | |
| Query OK, 8192 rows affected (0.09 sec) | |
| Records: 8192 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| update tq set k = mod(pk, 100) where j = 6 | |
| -------------- | |
| Query OK, 8110 rows affected (0.12 sec) | |
| Rows matched: 8192 Changed: 8110 Warnings: 0 | |
| -------------- | |
| insert into tq (pk,j,k) select null, 7, 7 from ti | |
| -------------- | |
| Query OK, 8192 rows affected (0.06 sec) | |
| Records: 8192 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| update tq set k = mod(pk, 100) where j = 7 | |
| -------------- | |
| Query OK, 8110 rows affected (0.09 sec) | |
| Rows matched: 8192 Changed: 8110 Warnings: 0 | |
| -------------- | |
| insert into tq (pk,j,k) select null, 8, 8 from ti | |
| -------------- | |
| Query OK, 8192 rows affected (0.05 sec) | |
| Records: 8192 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| update tq set k = mod(pk, 100) where j = 8 | |
| -------------- | |
| Query OK, 8110 rows affected (0.12 sec) | |
| Rows matched: 8192 Changed: 8110 Warnings: 0 | |
| -------------- | |
| insert into tq (pk,j,k) select null, 9, 9 from ti | |
| -------------- | |
| Query OK, 8192 rows affected (0.06 sec) | |
| Records: 8192 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| update tq set k = mod(pk, 100) where j = 9 | |
| -------------- | |
| Query OK, 8110 rows affected (0.10 sec) | |
| Rows matched: 8192 Changed: 8110 Warnings: 0 | |
| -------------- | |
| insert into tq (pk,j,k) select null, 10, 10 from ti | |
| -------------- | |
| Query OK, 8192 rows affected (0.10 sec) | |
| Records: 8192 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| update tq set k = mod(pk, 100) where j = 10 | |
| -------------- | |
| Query OK, 8110 rows affected (0.12 sec) | |
| Rows matched: 8192 Changed: 8110 Warnings: 0 | |
| -------------- | |
| CREATE INDEX x on tq(j, pk DESC) | |
| -------------- | |
| Query OK, 0 rows affected (1.38 sec) | |
| Records: 0 Duplicates: 0 Warnings: 0 | |
| -------------- | |
| ANALYZE TABLE tq | |
| -------------- | |
| +---------+---------+----------+----------+ | |
| | Table | Op | Msg_type | Msg_text | | |
| +---------+---------+----------+----------+ | |
| | test.tq | analyze | status | OK | | |
| +---------+---------+----------+----------+ | |
| 1 row in set (0.06 sec) | |
| -------------- | |
| explain format=tree | |
| SELECT max(pk), j FROM tq GROUP BY j | |
| -------------- | |
| *************************** 1. row *************************** | |
| EXPLAIN: -> Group aggregate (computed in earlier step): max(tq.pk) | |
| -> Index range scan on tq using index_for_group_by(x) (cost=13.00 rows=10) | |
| 1 row in set (0.00 sec) | |
| -------------- | |
| explain | |
| SELECT max(pk), j FROM tq GROUP BY j | |
| -------------- | |
| +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | |
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | |
| +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | |
| | 1 | SIMPLE | tq | NULL | range | x | x | 4 | NULL | 10 | 100.00 | Using index for group-by | | |
| +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | |
| 1 row in set, 1 warning (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=3.62 rows=10) | |
| -> Materialize | |
| -> Group aggregate (computed in earlier step): max(tq.pk) | |
| -> Index range scan on tq using index_for_group_by(x) (cost=13.00 rows=10) | |
| -> Single-row index lookup on t1 using PRIMARY (pk=t2.maxpk) (cost=0.26 rows=1) | |
| 1 row in set (0.00 sec) | |
| -------------- | |
| explain | |
| 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 | |
| -------------- | |
| +----+-------------+------------+------------+--------+---------------+---------+---------+----------+------+----------+--------------------------+ | |
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | |
| +----+-------------+------------+------------+--------+---------------+---------+---------+----------+------+----------+--------------------------+ | |
| | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where | | |
| | 1 | PRIMARY | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | t2.maxpk | 1 | 100.00 | NULL | | |
| | 2 | DERIVED | tq | NULL | range | x | x | 4 | NULL | 10 | 100.00 | Using index for group-by | | |
| +----+-------------+------------+------------+--------+---------------+---------+---------+----------+------+----------+--------------------------+ | |
| 3 rows in set, 1 warning (0.00 sec) | |
| -------------- | |
| explain format=tree | |
| SELECT pk, j, k FROM tq t1 | |
| WHERE pk=(SELECT MAX(t2.pk) FROM tq t2 WHERE t1.j = t2.j) | |
| -------------- | |
| *************************** 1. row *************************** | |
| EXPLAIN: -> Filter: (t1.pk = (select #2)) (cost=8273.25 rows=82170) | |
| -> Table scan on t1 (cost=8273.25 rows=82170) | |
| -> Select #2 (subquery in condition; dependent) | |
| -> Aggregate: max(t2.pk) | |
| -> Index lookup on t2 using x (j=t1.j) (cost=927.37 rows=9130) | |
| 1 row in set, 1 warning (0.00 sec) | |
| -------------- | |
| explain | |
| SELECT pk, j, k FROM tq t1 | |
| WHERE pk=(SELECT MAX(t2.pk) FROM tq t2 WHERE t1.j = t2.j) | |
| -------------- | |
| +----+--------------------+-------+------------+------+---------------+------+---------+-----------+-------+----------+-------------+ | |
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | |
| +----+--------------------+-------+------------+------+---------------+------+---------+-----------+-------+----------+-------------+ | |
| | 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 82170 | 100.00 | Using where | | |
| | 2 | DEPENDENT SUBQUERY | t2 | NULL | ref | x | x | 4 | test.t1.j | 9130 | 100.00 | Using index | | |
| +----+--------------------+-------+------------+------+---------------+------+---------+-----------+-------+----------+-------------+ | |
| 2 rows in set, 2 warnings (0.00 sec) | |
| -------------- | |
| explain format=tree | |
| 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 | |
| -------------- | |
| *************************** 1. row *************************** | |
| EXPLAIN: -> Filter: (t2.j is null) (cost=76209940.12 rows=750212100) | |
| -> Nested loop antijoin (cost=76209940.12 rows=750212100) | |
| -> Table scan on t1 (cost=8273.25 rows=82170) | |
| -> Filter: (t1.pk < t2.pk) (cost=14.38 rows=9130) | |
| -> Index lookup on t2 using x (j=t1.j) (cost=14.38 rows=9130) | |
| 1 row in set (0.00 sec) | |
| -------------- | |
| explain | |
| 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 | |
| -------------- | |
| +----+-------------+-------+------------+------+---------------+------+---------+-----------+-------+----------+--------------------------------------+ | |
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | |
| +----+-------------+-------+------------+------+---------------+------+---------+-----------+-------+----------+--------------------------------------+ | |
| | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 82170 | 100.00 | NULL | | |
| | 1 | SIMPLE | t2 | NULL | ref | PRIMARY,x | x | 4 | test.t1.j | 9130 | 100.00 | Using where; Not exists; Using index | | |
| +----+-------------+-------+------------+------+---------------+------+---------+-----------+-------+----------+--------------------------------------+ | |
| 2 rows in set, 1 warning (0.00 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=8273.25 rows=82170) | |
| -> Table scan on tq (cost=8273.25 rows=82170) | |
| 1 row in set (0.00 sec) | |
| -------------- | |
| explain | |
| 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 | |
| -------------- | |
| +----+-------------+------------+------------+------+---------------+-------------+---------+-------+-------+----------+----------------+ | |
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | |
| +----+-------------+------------+------------+------+---------------+-------------+---------+-------+-------+----------+----------------+ | |
| | 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 8 | const | 10 | 100.00 | NULL | | |
| | 2 | DERIVED | tq | NULL | ALL | NULL | NULL | NULL | NULL | 82170 | 100.00 | Using filesort | | |
| +----+-------------+------------+------------+------+---------------+-------------+---------+-------+-------+----------+----------------+ | |
| 2 rows in set, 2 warnings (0.00 sec) | |
| -------------- | |
| explain format=tree | |
| 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 | |
| -------------- | |
| *************************** 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=28759.75 rows=82170) | |
| -> Table scan on tq (cost=28759.75 rows=82170) | |
| 1 row in set, 2 warnings (0.00 sec) | |
| -------------- | |
| explain | |
| 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 | |
| -------------- | |
| +----+-------------+------------+------------+------+---------------+-------------+---------+-------+-------+----------+----------------+ | |
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | |
| +----+-------------+------------+------------+------+---------------+-------------+---------+-------+-------+----------+----------------+ | |
| | 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 8 | const | 10 | 100.00 | NULL | | |
| | 2 | DERIVED | tq | NULL | ALL | NULL | NULL | NULL | NULL | 82170 | 100.00 | Using filesort | | |
| +----+-------------+------------+------------+------+---------------+-------------+---------+-------+-------+----------+----------------+ | |
| 2 rows in set, 4 warnings (0.01 sec) | |
| -------------- | |
| explain SELECT /*+ NO_INDEX(tq x) */ DISTINCT(j) FROM tq | |
| -------------- | |
| +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------+ | |
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | |
| +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------+ | |
| | 1 | SIMPLE | tq | NULL | ALL | NULL | NULL | NULL | NULL | 82170 | 100.00 | Using temporary | | |
| +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------+ | |
| 1 row in set, 1 warning (0.00 sec) | |
| -------------- | |
| explain SELECT /*+ INDEX(tq x) */ DISTINCT(j) FROM tq | |
| -------------- | |
| +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | |
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | |
| +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | |
| | 1 | SIMPLE | tq | NULL | range | x | x | 4 | NULL | 10 | 100.00 | Using index for group-by | | |
| +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | |
| 1 row in set, 1 warning (0.00 sec) | |
| Bye |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment