order by キーカラムを含むインデックスの効果

• MySQL

(8.0.22)

drop table tab1;
drop table tab2;

create table tab1(col1 int, col2 int, col3 int, col4 int);
create table tab2(col1 int, col2 int, col3 int, col4 int);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);

drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i int;
set i = 1;
while i <= 1000000 do
insert into tab1 values(i,floor(rand() * 100000)+1,floor(rand() * 100)+1,floor(rand() * 100)+1 );
insert into tab2 values(i,floor(rand() * 100000)+1,floor(rand() * 100)+1,floor(rand() * 100)+1 );
set i = i + 1;
end while;
end
//
delimiter ;

call proc1();

select count(*) from tab1;
select count(*) from tab2;

drop index ind1 on tab1;
create index ind1 on tab1(col2);

drop index ind1 on tab1;
create index ind1 on tab1(col2,col3,col4);

explain
select t1.col1
from tab1 t1
where t1.col2 = 123
order by t1.col3,t1.col4;

→インデックスがorder by キーカラムを含む場合、Using filesortがない

explain
select t1.col1,t2.col1
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
where t1.col2 = 123
order by t1.col3,t1.col4;

→インデックスがorder by キーカラムを含む場合、Using filesortがない

• Oracle

(19c)

drop table tab1 purge;
drop table tab2 purge;

create table tab1(col1 int, col2 int, col3 int, col4 int);
create table tab2(col1 int, col2 int, col3 int, col4 int);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);

declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,floor(dbms_random.value(1, 100001)),floor(dbms_random.value(1, 101)),floor(dbms_random.value(1, 101)) );
insert into tab2 values(i,floor(dbms_random.value(1, 100001)),floor(dbms_random.value(1, 101)),floor(dbms_random.value(1, 101)) );
commit;
end loop;
end;
/

select count(*) from tab1;
select count(*) from tab2;

drop index ind1;
create index ind1 on tab1(col2);

drop index ind1;
create index ind1 on tab1(col2,col3,col4);

explain plan for
select t1.col1
from tab1 t1
where t1.col2 = 123
order by t1.col3,t1.col4;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

→インデックスがorder by キーカラムを含む場合、SORT ORDER BYがない

explain plan for
select t1.col1,t2.col1
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
where t1.col2 = 123
order by t1.col3,t1.col4;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

→インデックスがorder by キーカラムを含む場合、SORT ORDER BYがない

• PostgreSQL

(13)

drop table tab1;
drop table tab2;

create table tab1(col1 int, col2 int, col3 int, col4 int);
create table tab2(col1 int, col2 int, col3 int, col4 int);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);

do $$
declare
i int;
begin
i = 1;
while i <= 1000000 loop
insert into tab1 values(i,floor(random() * 100000)+1,floor(random() * 100)+1,floor(random() * 100)+1 );
insert into tab2 values(i,floor(random() * 100000)+1,floor(random() * 100)+1,floor(random() * 100)+1 );
i = i + 1;
end loop;
end
$$
;

select count(*) from tab1;
select count(*) from tab2;

drop index ind1;
create index ind1 on tab1(col2);

drop index ind1;
create index ind1 on tab1(col2,col3,col4);

/*+ IndexScan(t1 ind1) */
explain analyze
select t1.col1
from tab1 t1
where t1.col2 = 123
order by t1.col3,t1.col4;

→インデックスがorder by キーカラムを含む場合、Sortがない

/*+ IndexScan(t1 ind1) */
explain analyze
select t1.col1,t2.col1
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
where t1.col2 = 123
order by t1.col3,t1.col4;

→インデックスがorder by キーカラムを含む場合、Sortがない

• SQL Server

(2019)

drop table tab1;
drop table tab2;

create table tab1(col1 int not null, col2 int, col3 int, col4 int);
create table tab2(col1 int not null, col2 int, col3 int, col4 int);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);

declare @i int;
set @i = 1;

while (@i <= 1000000)
begin
insert into tab1 values(@i,floor(rand() * 100000)+1,floor(rand() * 100)+1,floor(rand() * 100)+1 );
insert into tab2 values(@i,floor(rand() * 100000)+1,floor(rand() * 100)+1,floor(rand() * 100)+1 );
set @i = @i + 1;
end

select count(*) from tab1;
select count(*) from tab2;

drop index ind1 on tab1;
create index ind1 on tab1(col2);

drop index ind1 on tab1;
create index ind1 on tab1(col2,col3,col4);

select t1.col1
from tab1 t1
where t1.col2 = 123
order by t1.col3,t1.col4;

→インデックスがorder by キーカラムを含む場合、Sortがない

select t1.col1,t2.col1
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
where t1.col2 = 123
order by t1.col3,t1.col4;

→インデックスがorder by キーカラムを含む場合、Sortがない