MySQLで区切り文字保存したフィールドの検索

前提

• MySQLで氏名を姓と名を1フィールドで保存。
• 姓と名の間に区切り文字として半角スペースを入れる。
• 姓で検索したい。名で検索したい。

テーブル

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
);

サンプル

mysql> SELECT * FROM `user`;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | 山田 太郎     |
|  2 | 山田 花子     |
+----+---------------+
2 rows in set (0.00 sec)

検索

• 姓で検索

mysql> SELECT * FROM `user` WHERE SUBSTRING_INDEX(`name`,' ',1) = '山田';
+----+---------------+
| id | name          |
+----+---------------+
|  1 | 山田 太郎     |
|  2 | 山田 花子     |
+----+---------------+
2 rows in set (0.01 sec)

• 名で検索

mysql> SELECT * FROM `user` WHERE SUBSTRING_INDEX(`name`,' ',-1) = '花子';
+----+---------------+
| id | name          |
+----+---------------+
|  2 | 山田 花子     |
+----+---------------+
1 row in set (0.00 sec)

(※注意)INDEXは?

• 効かない模様

mysql> EXPLAIN SELECT * FROM `user` WHERE SUBSTRING_INDEX(`name`,' ',1) = '山田';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM `user` WHERE `name` = '山田';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | user  | ref  | name          | name | 194     | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)