Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
output for setup
--------------
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
You can’t perform that action at this time.