インデックス周りの検証

README.md

事前準備

dockerでMySQLの環境を作る

CREATE DATABASE index_test;


CREATE TABLE `test` (
  id INT(11) NOT NULL AUTO_INCREMENT,
  string1 VARCHAR(64), -- 8桁のランダムな半角英数字が入る
  string2 VARCHAR(64), -- 8桁のランダムな半角英数字が入る
  created_date TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO test () Values ()
INSERT INTO test (id) SELECT 0 from test; これを繰り返してデータを量産する

// ランダムな文字列いれる
update test set string1 = SUBSTRING(MD5(RAND()), 1, 8), string2 = SUBSTRING(MD5(RAND()), 1, 8);
ALTER TABLE `test` ADD INDEX index_id(id)
ALTER TABLE `test` DROP INDEX index_id

ALTER TABLE `test` ADD INDEX index_string(string1)
ALTER TABLE `test` DROP INDEX index_string

• インデックスを付ける前の秒数

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|  2097152 |
+----------+
1 row in set (2.93 sec)

• idにインデックスつけるだけでcountもこんなに変わる

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|  2097152 |
+----------+
1 row in set (0.25 sec)

• インデックスをstring1のみにつけて秒数をみる

mysql> select count(*) from test where string1 like "abc%";
+----------+
| count(*) |
+----------+
|      570 |
+----------+
1 row in set (0.00 sec)

• インデックスをつけないとこんなに時間かかる

mysql> select count(*) from test where string1 like "abc%";
+----------+
| count(*) |
+----------+
|      570 |
+----------+
1 row in set (2.10 sec)

• string1とstring2にインデックスつけてみた

ALTER TABLE `test` ADD INDEX index_string(string1, string2);
mysql> select count(*) from test where string1 like "abc%" or string2 like "abc%";
+----------+
| count(*) |
+----------+
|     1070 |
+----------+
1 row in set (0.52 sec)


mysql> select count(*) from test where string2 like "abc%" or string1 like "abc%";
+----------+
| count(*) |
+----------+
|     1070 |
+----------+
1 row in set (0.50 sec)

• インデックス消すとこんなかんじ

mysql> select count(*) from test where string2 like "abc%" or string1 like "abc%";
+----------+
| count(*) |
+----------+
|     1070 |
+----------+
1 row in set (2.60 sec)

• インデックスなし

mysql> select * from test where string1 like "abc%" order by string2 desc;
570 rows in set (2.37 sec)

• 複合インデックスあり

mysql> select * from test where string1 like "abc%" order by string2 desc;
570 rows in set (0.02 sec)

でも逆向きだとかからないｗ
mysql> select * from test where string2 like "abc%" order by string1 desc;
500 rows in set (2.29 sec)