mysql index 検証①　基本編

CREATE TABLE data1(
  id INT(11) NOT NULL AUTO_INCREMENT,
  status TINYINT(1) NOT NULL,
  name VARCHAR(50) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

insert into data1(name,status) 
SELECT CONCAT ('test', @rownum := @rownum + lpad(FLOOR( (RAND() * 100000000)),9,'0')),
FLOOR( (RAND() * 2)) FROM data AS s1, (SELECT @rownum := 0) AS v;

mysql> explain  select * from data1 where name = "test000000000";
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | data1 | ALL  | NULL          | NULL | NULL    | NULL | 4193910 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

CREATE TABLE data2(
  id INT(11) NOT NULL AUTO_INCREMENT,
  status TINYINT(1) NOT NULL,
  name VARCHAR(50) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_1(name)
);

insert into data2(name,status) 
SELECT CONCAT ('test', @rownum := @rownum + lpad(FLOOR( (RAND() * 100000000)),9,'0')),
FLOOR( (RAND() * 2)) FROM data AS s1, (SELECT @rownum := 0) AS v;

mysql> explain  select * from data2 where name = "test000000000";
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | data2 | ref  | idx_1         | idx_1 | 52      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
1 row in set (0.02 sec)

CREATE TABLE data3(
  id INT(11) NOT NULL AUTO_INCREMENT,
  status TINYINT(1) NOT NULL,
  name VARCHAR(50) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_1(status,name)
);

insert into data3(name,status) 
SELECT CONCAT ('test', @rownum := @rownum + lpad(FLOOR( (RAND() * 100000000)),9,'0')),
FLOOR( (RAND() * 2)) FROM data AS s1, (SELECT @rownum := 0) AS v;

mysql> explain  select * from data3 where name = "test000000000";
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | data3 | ALL  | NULL          | NULL | NULL    | NULL | 4183161 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

インデックスはstatus,nameの順の為、
nameだけ指定した検索ではインデックスが使用されない。

その為
mysql> explain  select * from data3 where name = "test000000000" and (status = 0 or status= 1 );
+----+-------------+-------+-------+---------------+-------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-----------------------+
|  1 | SIMPLE      | data3 | range | idx_1         | idx_1 | 53      | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
不要でも、statusを含め無いとインデックスを使用してくれない。

CREATE TABLE data4(
  id INT(11) NOT NULL AUTO_INCREMENT,
  status TINYINT(1) NOT NULL,
  name VARCHAR(50) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_1(name,status)
);

insert into data4(name,status) 
SELECT CONCAT ('test', @rownum := @rownum + lpad(FLOOR( (RAND() * 100000000)),9,'0')),
FLOOR( (RAND() * 2)) FROM data AS s1, (SELECT @rownum := 0) AS v;

mysql> explain  select * from data4 where name = "test000000000";
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | data4 | ref  | idx_1         | idx_1 | 52      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

CREATE TABLE data5(
  id INT(11) NOT NULL AUTO_INCREMENT,
  status TINYINT(1) NOT NULL,
  name VARCHAR(50) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_1(status),
  INDEX idx_2(name)
);

insert into data5(name,status) 
SELECT CONCAT ('test', @rownum := @rownum + lpad(FLOOR( (RAND() * 100000000)),9,'0')),
FLOOR( (RAND() * 2)) FROM data AS s1, (SELECT @rownum := 0) AS v;

mysql> explain  select * from data5 where name = "test000000000";
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | data5 | ref  | idx_2         | idx_2 | 52      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)