gistfile1.txt

drop table if exists ten;
drop table if exists one_k;
drop table if exists tq;

create table ten(a int primary key);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table one_k(a int primary key);
insert into one_k select a.a + b.a* 10 + c.a * 100 from ten a, ten b, ten c;
#insert into one_k select a.a + b.a* 10 from ten a, ten b;

create table tq (
  pk int primary key auto_increment,
  j int,
  k int
);
create index jxa on tq(j);

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;

analyze table tq;

# try with index on tq(j)

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

flush status;
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;

show status like 'Handler%';
show status like '%sort%';

# try with index on tq(j, pk)

alter table tq drop index jxa;
create index jx2 on tq(j, pk);
analyze table tq;

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

flush status;
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;

show status like 'Handler%';
show status like '%sort%';

# try with index on tq(j, pk DESC)

alter table tq drop index jx2;
create index jxd2 on tq(j, pk DESC);
analyze table tq;

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

flush status;
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;

show status like 'Handler%';
show status like '%sort%';

# try with 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

flush status;
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;

show status like 'Handler%';
show status like '%sort%';