SQLアンチパターン：6章ポリモーフィック関連

gistfile1.md

6章 ポリモーフィック関連

準備

create database chapter6;
user chapter6;

6.2.2 ポリモーフィック関連へのクエリ実行

準備

create table Accounts (
  account_id bigint unsigned not null auto_increment,
  account_name varchar(1000),
  primary key (`account_id`)
);

create table Comments (
  comment_id   SERIAL PRIMARY KEY,
  issue_type   VARCHAR(20),     -- "Bugs" or "FeatureRequests"
  issue_id     BIGINT UNSIGNED NOT NULL,
  author       BIGINT UNSIGNED NOT NULL,
  comment_date DATETIME,
  comment      TEXT,
  FOREIGN KEY (author) REFERENCES Accounts(account_id)
);

create table Bugs (
  issue_id BIGINT UNSIGNED NOT NULL auto_increment,
  primary key(`issue_id`)
);

insert into Bugs value(1234);

create table FeatureRequests (
  issue_id BIGINT UNSIGNED NOT NULL auto_increment,
  primary key(`issue_id`)
);

insert into FeatureRequests value(2345);

insert into Accounts values(1,'hironomiu');

insert into Comments values(6789,'Bugs',1234,1,now(),'クラッシュします');
insert into Comments values(9876,'FeatureRequests',2345,1,now(),'いいアイディア！');

検索、実行計画の確認

select * from Bugs as b inner join Comments as c on b.issue_id = c.issue_id and c.issue_type = 'Bugs' where b.issue_id = 1234;

select * from Comments AS c LEFT OUTER JOIN Bugs AS b ON (b.issue_id = c.issue_id AND c.issue_type = 'Bugs') LEFT OUTER JOIN FeatureRequests AS f ON (f.issue_id = c.issue_id AND c.issue_type = 'FeatureRequests');

explain select * from Comments AS c LEFT OUTER JOIN Bugs AS b ON (b.issue_id = c.issue_id AND c.issue_type = 'Bugs') LEFT OUTER JOIN FeatureRequests AS f ON (f.issue_id = c.issue_id AND c.issue_type = 'FeatureRequests')\G

select * from Comments AS c
  LEFT OUTER JOIN Bugs AS b ON (b.issue_id = c.issue_id AND c.issue_type = 'Bugs')
  LEFT OUTER JOIN FeatureRequests AS f ON (f.issue_id = c.issue_id AND c.issue_type = 'FeatureRequests');