[データベースシステム論] インデクスの効果測定用テーブルの作成と測定

step1 テーブル作成.sql

--zipcodeと同じスキーマのテーブルzipcode2を作成
CREATE TABLE zipcode2
( 
  zip integer, 
  kana1 varchar(32), 
  kana2 varchar(32), 
  kana3 varchar(128), 
  addr1 varchar(32), 
  addr2 varchar(32), 
  addr3 varchar(128)
);

--データの流し込み
INSERT INTO zipcode2 
  SELECT * FROM zipcode;
 
--zipxode2.zipにbtreeインデクス作成
CREATE INDEX idx_zipcode2_zip 
  ON zipcode2 
  USING btree (zip);

--zipxode2.addr3にhashインデクス作成
CREATE INDEX idx_zipcode2_addr3 
  ON zipcode2 
  USING hash (addr3);

step2 コスト測定.sql

------------------------------------------------------------効果測定１
--btreeインデクス(等価検索)
---インデクスなし
EXPLAIN 
SELECT * FROM zipcode WHERE zip = 4328011;

---インデクスあり
EXPLAIN 
SELECT * FROM zipcode2 WHERE zip = 4328011;

------------------------------------------------------------効果測定２
--btreeインデクス(範囲検索)
---インデクスなし
EXPLAIN 
SELECT * FROM zipcode WHERE zip >= 4328011 AND zip <= 4328066 ;

---インデクスあり
EXPLAIN 
SELECT * FROM zipcode2 WHERE zip >= 4328011 AND zip <= 4328066 ;

------------------------------------------------------------効果測定３
--hashインデクス(完全一致)
---インデクスなし
EXPLAIN 
SELECT * FROM zipcode WHERE addr3 = '城北';

---インデクスあり
EXPLAIN 
SELECT * FROM zipcode2 WHERE addr3 = '城北';

------------------------------------------------------------効果測定４
--hashインデクス(部分一致)
---インデクスなし
EXPLAIN 
SELECT * FROM zipcode WHERE addr3 LIKE '%城%';

---インデクスあり
EXPLAIN 
SELECT * FROM zipcode2 WHERE addr3 LIKE '%城%';