Skip to content

Instantly share code, notes, and snippets.

@sakama

sakama/MySQL5.6のFulltextIndexのベンチマーク結果.md

Created July 22, 2014 10:59
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save sakama/52aa2ab4769e773fbaca to your computer and use it in GitHub Desktop.
Save sakama/52aa2ab4769e773fbaca to your computer and use it in GitHub Desktop.
Download ZIP
MySQL5.6のFulltextIndexのベンチマーク結果
Raw
MySQL5.6のFulltextIndexのベンチマーク結果.md

MySQL5.5

text型

mysql> explain select * from restaurants where name like "あ%";
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | restaurants | ALL  | NULL          | NULL | NULL    | NULL | 211793 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.01 sec)

mysql> explain select * from restaurants where name like "%あ%";
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | restaurants | ALL  | NULL          | NULL | NULL    | NULL | 211793 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.01 sec)

mysql> explain select * from restaurants where name like "%あ";
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | restaurants | ALL  | NULL          | NULL | NULL    | NULL | 211793 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.01 sec)

varchar(255)でindexを貼った

mysql> ALTER TABLE  `restaurants` CHANGE  `name`  `name` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;

mysql> ALTER TABLE `restaurants` ADD INDEX  `idx_name` (  `name` ) COMMENT  '';

mysql> explain select * from restaurants where name like "あ%";
+----+-------------+-------------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table       | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | restaurants | range | idx_name      | idx_name | 767     | NULL | 1288 | Using where |
+----+-------------+-------------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.01 sec)

mysql> explain select * from restaurants where name like "あ%";
+----+-------------+-------------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table       | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | restaurants | range | idx_name      | idx_name | 767     | NULL | 1288 | Using where |
+----+-------------+-------------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.01 sec)

mysql> explain select * from restaurants where name like "%あ%";
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | restaurants | ALL  | NULL          | NULL | NULL    | NULL | 208434 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.01 sec)

mysql> explain select * from restaurants where name like "%あ";
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | restaurants | ALL  | NULL          | NULL | NULL    | NULL | 208434 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.01 sec)

MySQL5.6

text型

mysql> explain select * from restaurants where name like "あ%";
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | restaurants | ALL  | NULL          | NULL | NULL    | NULL | 214008 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.03 sec)

mysql> explain select * from restaurants where name like "%あ%";
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | restaurants | ALL  | NULL          | NULL | NULL    | NULL | 214008 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from restaurants where name like "%あ";
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | restaurants | ALL  | NULL          | NULL | NULL    | NULL | 214008 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

varchar(255)でindexを貼った

mysql> ALTER TABLE  `restaurants` CHANGE  `name`  `name` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;

mysql> ALTER TABLE `restaurants` ADD INDEX  `idx_name` (  `name` ) COMMENT  '';

mysql> explain select * from restaurants where name like "あ%";
+----+-------------+-------------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table       | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | restaurants | range | idx_name      | idx_name | 767     | NULL | 1288 | Using index condition |
+----+-------------+-------------+-------+---------------+----------+---------+------+------+-----------------------+
1 row in set (0.01 sec)

mysql> explain select * from restaurants where name like "%あ%";
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | restaurants | ALL  | NULL          | NULL | NULL    | NULL | 201385 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.01 sec)

mysql> explain select * from restaurants where name like "%あ";
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | restaurants | ALL  | NULL          | NULL | NULL    | NULL | 201385 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.01 sec)

Fulltext Index

mysql> ALTER TABLE  `restaurants` CHANGE  `name`  `name` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;

mysql> alter table restaurants add fulltext(`name`);

mysql> explain select * from restaurants where match(name) against('あ+');
+----+-------------+-------------+----------+---------------+------+---------+------+------+-------------+
| id | select_type | table       | type     | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+----------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | restaurants | fulltext | name          | name | 0       | NULL |    1 | Using where |
+----+-------------+-------------+----------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from restaurants where match(name) against('+あ+');
+----+-------------+-------------+----------+---------------+------+---------+------+------+-------------+
| id | select_type | table       | type     | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+----------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | restaurants | fulltext | name          | name | 0       | NULL |    1 | Using where |
+----+-------------+-------------+----------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

mysql> explain select * from restaurants where match(name) against('+あ');
+----+-------------+-------------+----------+---------------+------+---------+------+------+-------------+
| id | select_type | table       | type     | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+----------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | restaurants | fulltext | name          | name | 0       | NULL |    1 | Using where |
+----+-------------+-------------+----------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment