Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
setup for maxrow
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;
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.