setup for maxrow

gistfile1.txt

drop table if exists ti; drop table if exists tq;

create table ti(i int);
create table tq(pk int primary key auto_increment, j int NOT NULL, k int NOT NULL);

insert into ti values (null), (null), (null), (null);
insert into ti select null from ti;
insert into ti select null from ti;
insert into ti select null from ti;
insert into ti select null from ti;
insert into ti select null from ti;
insert into ti select null from ti;
insert into ti select null from ti;
insert into ti select null from ti;
insert into ti select null from ti;
insert into ti select null from ti;
insert into ti select null from ti;

insert into tq (pk,j,k) select null, 1, 1 from ti;
update tq set k = mod(pk, 100) where j = 1;
insert into tq (pk,j,k) select null, 2, 2 from ti;
update tq set k = mod(pk, 100) where j = 2;
insert into tq (pk,j,k) select null, 3, 3 from ti;
update tq set k = mod(pk, 100) where j = 3;
insert into tq (pk,j,k) select null, 4, 4 from ti;
update tq set k = mod(pk, 100) where j = 4;
insert into tq (pk,j,k) select null, 5, 5 from ti;
update tq set k = mod(pk, 100) where j = 5;
insert into tq (pk,j,k) select null, 6, 6 from ti;
update tq set k = mod(pk, 100) where j = 6;
insert into tq (pk,j,k) select null, 7, 7 from ti;
update tq set k = mod(pk, 100) where j = 7;
insert into tq (pk,j,k) select null, 8, 8 from ti;
update tq set k = mod(pk, 100) where j = 8;
insert into tq (pk,j,k) select null, 9, 9 from ti;
update tq set k = mod(pk, 100) where j = 9;
insert into tq (pk,j,k) select null, 10, 10 from ti;
update tq set k = mod(pk, 100) where j = 10;

CREATE INDEX x on tq(j, pk DESC);
ANALYZE TABLE tq;

# Query with loose index scan optimization

explain format=tree
SELECT max(pk), j FROM tq GROUP BY j \G

explain
SELECT max(pk), j FROM tq GROUP BY j;

# Uncorrelated subquery

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

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;

# Correlated subquery

explain format=tree
SELECT pk, j, k FROM   tq t1
WHERE  pk=(SELECT MAX(t2.pk) FROM tq t2 WHERE t1.j = t2.j) \G

explain
SELECT pk, j, k FROM  tq t1
WHERE  pk=(SELECT MAX(t2.pk) FROM tq t2 WHERE t1.j = t2.j);

# Left join

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

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;

# rank() window function with DESC

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

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;

# rank() window function with DESC and index hint

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

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;

# Test for index hints
explain SELECT /*+ NO_INDEX(tq x) */ DISTINCT(j) FROM tq;
explain SELECT /*+ INDEX(tq x) */ DISTINCT(j) FROM tq;