2013/05/29リリースの Mroonga 3.04 の新機能として、TritonnにあったWプラグマのバックポート(?)があります。重み付け検索というより、マルチセクションインデックスの一部を使って検索するために使うことが多いような気もします。
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`val1` varchar(20) DEFAULT NULL,
`val2` varchar(20) DEFAULT NULL,
`val3` varchar(20) DEFAULT NULL,
UNIQUE KEY `num` (`num`),
FULLTEXT KEY `val1` (`val1`,`val2`,`val3`)
) ENGINE=mroonga AUTO_INCREMENT=100002 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> SELECT * FROM t1 WHERE MATCH(val1) AGAINST('デコ助' IN BOOLEAN MODE) ORDER BY num LIMIT 3;
ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list
mysql> SELECT * FROM t1 WHERE MATCH(val1, val2, val3) AGAINST('+デコ助' IN BOOLEAN MODE) ORDER BY num LIMIT 3;
+-----+---------------------------------------+--------------------------------------+---------------------------------------------+
| num | val1 | val2 | val3 |
+-----+---------------------------------------+--------------------------------------+---------------------------------------------+
| 5 | アイドルマスター | アウトブレイク奇妙な冒険 | 人類はデコ助野郎! |
| 9 | 13日のデコ助野郎! | 恋と選挙と恋がしたい! | ジョジョのUC[ユニコーン] |
| 14 | アウトブレイクデコ助野郎! | 魔法少女カンパニー | コードギアス亡国の恋がしたい! |
+-----+---------------------------------------+--------------------------------------+---------------------------------------------+
3 rows in set (0.01 sec)
mysql> SELECT * FROM t1 WHERE MATCH(val1, val2, val3) AGAINST('*W0:1,1:0,2:0 +デコ助' IN BOOLEAN MODE) ORDER BY num LIMIT 3;
+-----+---------------------------------------+---------------------------------+---------------------------------------------+
| num | val1 | val2 | val3 |
+-----+---------------------------------------+---------------------------------+---------------------------------------------+
| 9 | 13日のデコ助野郎! | 恋と選挙と恋がしたい! | ジョジョのUC[ユニコーン] |
| 14 | アウトブレイクデコ助野郎! | 魔法少女カンパニー | コードギアス亡国の恋がしたい! |
| 115 | インフィニットデコ助野郎! | 僕は友達が金曜日 | 魔法少女＠がんばらない |
+-----+---------------------------------------+---------------------------------+---------------------------------------------+
3 rows in set (0.02 sec)
val1からだけ"デコ助"を検索したいのですが、val1だけに張られたフルテキストインデックスは無いので怒られます(1つ目のSELECT)し、かといってMATCH(val1, val2, val3)とするとval1, val2, val3全てから"デコ助"を探してしまいます。Wプラグマがあれば、重み付けでカラム0(=val1)の重みを1に、カラム1(=val2)とカラム2(=val3)の重みを0にしてやることで、val1からだけ検索することができます。
重みも考慮した検索結果を返したいときはORDER BYを併用して
mysql> SELECT *, MATCH(val1, val2, val3) AGAINST('+デコ助' IN BOOLEAN MODE) AS score FROM t1 WHERE MATCH(val1, val2, val3) AGA
INST('+デコ助' IN BOOLEAN MODE) ORDER BY score DESC, num ASC LIMIT 3;
+-------+---------------------------+---------------------------------------+---------------------------------------+-------+
| num | val1 | val2 | val3 | score |
+-------+---------------------------+---------------------------------------+---------------------------------------+-------+
| 8808 | 13日のデコ助野郎! | さんをつけろよデコ助野郎! | さんをつけろよデコ助野郎! | 3 |
| 10139 | 進撃のデコ助野郎! | ささみさんデコ助野郎! | インフィニットデコ助野郎! | 3 |
| 12213 | 人類はデコ助野郎! | 僕は友達がデコ助野郎! | アイドルデコ助野郎! | 3 |
+-------+---------------------------+---------------------------------------+---------------------------------------+-------+
3 rows in set (0.02 sec)
mysql> SELECT *, MATCH(val1, val2, val3) AGAINST('*W0:2,1:1,2:1 +デコ助' IN BOOLEAN MODE) AS score FROM t1 WHERE MATCH(val1, val2, val3) AGAINST('*W0:2,1:1,2:1 +デコ助' IN BOOLEAN MODE) ORDER BY score DESC, num LIMIT 3;
+-------+---------------------------+---------------------------------------+---------------------------------------+-------+
| num | val1 | val2 | val3 | score |
+-------+---------------------------+---------------------------------------+---------------------------------------+-------+
| 8808 | 13日のデコ助野郎! | さんをつけろよデコ助野郎! | さんをつけろよデコ助野郎! | 4 |
| 10139 | 進撃のデコ助野郎! | ささみさんデコ助野郎! | インフィニットデコ助野郎! | 4 |
| 12213 | 人類はデコ助野郎! | 僕は友達がデコ助野郎! | アイドルデコ助野郎! | 4 |
+-------+---------------------------+---------------------------------------+---------------------------------------+-------+
3 rows in set (0.03 sec)
スコアが大きい順番に並べてみたりできます。スコアだけでORDER BYしている場合、スコアが同じタプルが存在する場合、どんな順番で返すかは保証されません。たぶん.mrnファイルの内部構造に依存するんだと思います。
全ての重みが均等(Wプラグマの中で重みを指定しなかった場合、全部のカラムが重み1で扱われる)の時のスコアに比べて、val1の重みを2にした時のスコアが上がってます。わかりやすい。
さて、Wプラグマが無かった昔(といっても、半年ちょっと前のことだし、簡単にMroongaをアップグレードする訳にいかない人たちは今なお)、重み付け検索をするにはどうすれば良かったんだろうという *思考実験* です。
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`val1` varchar(20) DEFAULT NULL,
`val2` varchar(20) DEFAULT NULL,
`val3` varchar(20) DEFAULT NULL,
UNIQUE KEY `num` (`num`),
FULLTEXT KEY `val1` (`val1`),
FULLTEXT KEY `val2` (`val2`),
FULLTEXT KEY `val3` (`val3`)
) ENGINE=mroonga AUTO_INCREMENT=100002 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> SELECT *, MATCH(val1) AGAINST('+デコ助' IN BOOLEAN MODE)* 2+ MATCH(val2) AGAINST('+デコ助' IN BOOLEAN MODE)+ MATCH(val3) AGAINST('+デコ助' IN BOOLEAN MODE) AS score FROM t1 WHERE MATCH(val1) AGAINST('+デコ助' IN BOOLEAN MODE)* 2+ MATCH(val2) AGAINST('+デコ助' IN BOOLEAN MODE)+ MATCH(val3) AGAINST('+デコ助' IN BOOLEAN MODE) ORDER BY score DESC, num ASC LIMIT 3;
+-------+---------------------------+---------------------------------------+---------------------------------------+-------+
| num | val1 | val2 | val3 | score |
+-------+---------------------------+---------------------------------------+---------------------------------------+-------+
| 8808 | 13日のデコ助野郎! | さんをつけろよデコ助野郎! | さんをつけろよデコ助野郎! | 4 |
| 10139 | 進撃のデコ助野郎! | ささみさんデコ助野郎! | インフィニットデコ助野郎! | 4 |
| 12213 | 人類はデコ助野郎! | 僕は友達がデコ助野郎! | アイドルデコ助野郎! | 4 |
+-------+---------------------------+---------------------------------------+---------------------------------------+-------+
3 rows in set (0.33 sec)
クエリーが見にくい上に重い。。容量効率も悪そうですが、他にぱっと思い付きませんでした。
プロファイルの結果はこんなかんじ（Warningが出てるのは5.6では@@profileはdeprecatedだよってアレ)
mysql> SHOW PROFILE FOR QUERY 54;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000037 |
| Waiting for query cache lock | 0.000005 |
| init | 0.000004 |
| checking query cache for query | 0.000164 |
| checking permissions | 0.000011 |
| Opening tables | 0.000042 |
| init | 0.000063 |
| System lock | 0.000043 |
| Waiting for query cache lock | 0.000004 |
| System lock | 0.000031 |
| optimizing | 0.000017 |
| statistics | 0.000365 |
| preparing | 0.000027 |
| FULLTEXT initialization | 0.020383 |
| Sorting result | 0.000022 |
| executing | 0.000005 |
| Sending data | 0.000018 |
| Creating sort index | 0.302915 |
| end | 0.000026 |
| query end | 0.000008 |
| closing tables | 0.000212 |
| freeing items | 0.000355 |
| Waiting for query cache lock | 0.000007 |
| freeing items | 0.000350 |
| Waiting for query cache lock | 0.000006 |
| freeing items | 0.000004 |
| storing result in query cache | 0.000006 |
| cleaning up | 0.000034 |
+--------------------------------+----------+
28 rows in set, 1 warning (0.01 sec)
mysql> explain SELECT *, MATCH(val1) AGAINST('+デコ助' IN BOOLEAN MODE)* 2+ MATCH(val2) AGAINST('+デコ助' IN BOOLEAN MODE)+ MATCH(val3) AGAINST('+デコ助' IN BOOLEAN MODE) AS score FROM t1 WHERE MATCH(val1) AGAINST('+デコ助' IN BOOLEAN MODE)* 2+ MATCH(val2) AGAINST('+デコ助' IN BOOLEAN MODE)+ MATCH(val3) AGAINST('+デコ助' IN BOOLEAN MODE) ORDER BY score DESC, num ASC LIMIT 3;
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 100001 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)
EXPLAINがALLになっちゃった(´･ω･｀) ほんとかな。
こっちがWプラグマでさっくりやった時のプロファイル。
mysql> show profile;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000039 |
| Waiting for query cache lock | 0.000004 |
| init | 0.000004 |
| checking query cache for query | 0.000135 |
| checking permissions | 0.000011 |
| Opening tables | 0.000041 |
| init | 0.000068 |
| System lock | 0.000020 |
| Waiting for query cache lock | 0.000003 |
| System lock | 0.000024 |
| optimizing | 0.000017 |
| statistics | 0.000423 |
| preparing | 0.000028 |
| FULLTEXT initialization | 0.018467 |
| Sorting result | 0.000028 |
| executing | 0.000005 |
| Sending data | 0.000024 |
| Creating sort index | 0.000195 |
| end | 0.000010 |
| query end | 0.000010 |
| closing tables | 0.000184 |
| freeing items | 0.000178 |
| Waiting for query cache lock | 0.000006 |
| freeing items | 0.000315 |
| Waiting for query cache lock | 0.000006 |
| freeing items | 0.000003 |
| storing result in query cache | 0.000006 |
| cleaning up | 0.000037 |
+--------------------------------+----------+
28 rows in set, 1 warning (0.00 sec)
mysql> explain SELECT *, MATCH(val1, val2, val3) AGAINST('*W0:2,1:1,2:1 +デコ助' IN BOOLEAN MODE) AS score FROM t1 WHERE MATCH(val1, val2, val3) AGAINST('*W0:2,1:1,2:1 +デコ助' IN BOOLEAN MODE) ORDER BY score DESC, num ASC LIMIT 3;
+----+-------------+-------+----------+---------------+------+---------+------+------+---------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+----------+---------------+------+---------+------+------+---------------------------------------------------+
| 1 | SIMPLE | t1 | fulltext | val1 | val1 | 0 | NULL | 1 | Using where with pushed condition; Using filesort |
+----+-------------+-------+----------+---------------+------+---------+------+------+---------------------------------------------------+
1 row in set (0.00 sec)
意外なのは、FULLTEXT initやfreeing itemsはそこまで盛大に時間がかかる訳じゃなく、ソートの部分(Creating sort index)でのみ大きく差が出ていること。
mysql> SELECT *, MATCH(val1, val2, val3) AGAINST('+デコ助' IN BOOLEAN MODE)+ (val1 LIKE '%デコ助%') AS score FROM t1 WHERE MATCH(val1, val2, val3) AGAINST('+デコ助' IN BOOLEAN MODE) ORDER BY score DESC, num ASC LIMIT 3;
+-------+---------------------------+---------------------------------------+---------------------------------------+-------+
| num | val1 | val2 | val3 | score |
+-------+---------------------------+---------------------------------------+---------------------------------------+-------+
| 8808 | 13日のデコ助野郎! | さんをつけろよデコ助野郎! | さんをつけろよデコ助野郎! | 4 |
| 10139 | 進撃のデコ助野郎! | ささみさんデコ助野郎! | インフィニットデコ助野郎! | 4 |
| 12213 | 人類はデコ助野郎! | 僕は友達がデコ助野郎! | アイドルデコ助野郎! | 4 |
+-------+---------------------------+---------------------------------------+---------------------------------------+-------+
3 rows in set (0.09 sec)
mysql> explain SELECT *, MATCH(val1, val2, val3) AGAINST('+デコ助' IN BOOLEAN MODE)+ (val1 LIKE '%デコ助%') AS score FROM t1 WHERE MATCH(val1, val2, val3) AGAINST('+デコ助' IN BOOLEAN MODE) ORDER BY score DESC, num ASC LIMIT 3;
+----+-------------+-------+----------+---------------+------+---------+------+------+---------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+----------+---------------+------+---------+------+------+---------------------------------------------------+
| 1 | SIMPLE | t1 | fulltext | val1 | val1 | 0 | NULL | 1 | Using where with pushed condition; Using filesort |
+----+-------------+-------+----------+---------------+------+---------+------+------+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW PROFILE FOR QUERY 77;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000055 |
| Waiting for query cache lock | 0.000005 |
| init | 0.000004 |
| checking query cache for query | 0.000141 |
| checking permissions | 0.000011 |
| Opening tables | 0.000043 |
| init | 0.000070 |
| System lock | 0.000019 |
| Waiting for query cache lock | 0.000003 |
| System lock | 0.000018 |
| optimizing | 0.000015 |
| statistics | 0.000356 |
| preparing | 0.000025 |
| FULLTEXT initialization | 0.011795 |
| Sorting result | 0.000020 |
| executing | 0.000004 |
| Sending data | 0.000018 |
| Creating sort index | 0.075364 |
| end | 0.000023 |
| query end | 0.000009 |
| closing tables | 0.000201 |
| freeing items | 0.000181 |
| Waiting for query cache lock | 0.000006 |
| freeing items | 0.000318 |
| Waiting for query cache lock | 0.000006 |
| freeing items | 0.000002 |
| storing result in query cache | 0.000007 |
| cleaning up | 0.000033 |
+--------------------------------+----------+
28 rows in set, 1 warning (0.00 sec)
重みをつけるカラムが大したことない数の場合は、(val1 LIKE '%..%')で無理矢理重みをつけてしまう(LIKE句にマッチした場合は1, マッチしない場合は0が戻るので、掛け算なり足し算なりある程度スコアとして使える)
こっちの方がまだマシそうな速度。。ただしクエリーはいやんな感じだなあ。。
やっぱりおとなしく最新のMroonga使うべきですね！ :)
ちなみに、テストデータは MySQL Casual Advent Calendar 2013 の13日目、
0 件のコメント :
コメントを投稿