Re: mroonga検索スコア順のソートについて (groonga-dev,00986) - Groonga - fulltext search engine.

斯波です。

> （order by 句の）MATCH AGAINSTで指定したカラムにヒットするものだけが出力
> されます。
> ※これはmroongaの仕様ではなく、MySQLのMATCH AGAINSTの仕様です。
>
> http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html
すみません。
こちらは具体的に頂いたページのどの文言になりますでしょうか？

MyISAMとmroongaを利用して実際に挙動を比較してみると、気になる違い
(以下の◎の部分)が認められまして、もう少し詳しく挙動の違いについて
調査してみようと思っています。

---- MyISAM ----

MySQL [test3]> create table ft(a int, b text, c text, primary key(a),
fulltext key ftx1(b), fulltext key ftx2(c))engine=myisam default
charset=utf8;
Query OK, 0 rows affected (0.06 sec)

MySQL [test3]> insert into ft values(1,'aaaaa','abcde');
Query OK, 1 row affected (0.01 sec)

MySQL [test3]> insert into ft values(2,'bbbbb','bcdef');
Query OK, 1 row affected (0.00 sec)

MySQL [test3]> insert into ft values(3,'ccccc','cdefg');
Query OK, 1 row affected (0.00 sec)

MySQL [test3]> insert into ft values(4,'ddddd','defgh');
Query OK, 1 row affected (0.01 sec)

MySQL [test3]> insert into ft values(5,'eeeee','efghi');
Query OK, 1 row affected (0.01 sec)

MySQL [test3]> select a, b, c from ft where match(b) against('bbbbb'
in boolean mode);
+---+-------+-------+
| a | b     | c     |
+---+-------+-------+
| 2 | bbbbb | bcdef |
+---+-------+-------+
1 row in set (0.00 sec)

MySQL [test3]> select a, b, c from ft where match(b) against('bbbbb'
in boolean mode) order by match(b) against('bbbbb' in boolean mode);
select a, b, c from ft where match(b) against('bbbbb' in boolean mode)
order by match(c) against('bbbbb' in boolean mode);
+---+-------+-------+
| a | b     | c     |
+---+-------+-------+
| 2 | bbbbb | bcdef |
+---+-------+-------+
1 row in set (0.01 sec)

◎
MySQL [test3]> select a, b, c from ft where match(b) against('bbbbb'
in boolean mode) order by match(c) against('bbbbb' in boolean mode);
+---+-------+-------+
| a | b     | c     |
+---+-------+-------+
| 2 | bbbbb | bcdef |
+---+-------+-------+
1 row in set (0.00 sec)

MySQL [test3]> select a, b, c from ft where match(c) against('bbbbb'
in boolean mode);
Empty set (0.00 sec)

MySQL [test3]> select a, b, c, match(b) against('bbbbb' in boolean
mode), match(c) against('bbbbb' in boolean mode) from ft where
match(b) against('bbbbb' in boolean mode) order by match(c)
against('bbbbb' in boolean mode);
+---+-------+-------+-------------------------------------------+-------------------------------------------+
| a | b     | c     | match(b) against('bbbbb' in boolean mode) |
match(c) against('bbbbb' in boolean mode) |
+---+-------+-------+-------------------------------------------+-------------------------------------------+
| 2 | bbbbb | bcdef |                                         1 |
                                   0 |
+---+-------+-------+-------------------------------------------+-------------------------------------------+
1 row in set (0.01 sec)

◎
MySQL [test3]> select a, b, c, match(b) against('bbbbb' in boolean
mode), match(c) against('bbbbb' in boolean mode) from ft where
match(b) against('bbbbb' in boolean mode);
+---+-------+-------+-------------------------------------------+-------------------------------------------+
| a | b     | c     | match(b) against('bbbbb' in boolean mode) |
match(c) against('bbbbb' in boolean mode) |
+---+-------+-------+-------------------------------------------+-------------------------------------------+
| 2 | bbbbb | bcdef |                                         1 |
                                   0 |
+---+-------+-------+-------------------------------------------+-------------------------------------------+
1 row in set (0.01 sec)


---- mroonga ----

MySQL [test4]> create table ft(a int, b text, c text, primary key(a),
fulltext key ftx1(b), fulltext key ftx2(c))engine=mroonga default
charset=utf8;
Query OK, 0 rows affected (0.51 sec)

MySQL [test4]> insert into ft values(1,'aaaaa','abcde');
insert into ft values(2,'bbbbb','bcdef');
Query OK, 1 row affected (0.02 sec)

MySQL [test4]> insert into ft values(2,'bbbbb','bcdef');
Query OK, 1 row affected (0.00 sec)

MySQL [test4]> insert into ft values(3,'ccccc','cdefg');
Query OK, 1 row affected (0.00 sec)

MySQL [test4]> insert into ft values(4,'ddddd','defgh');
Query OK, 1 row affected (0.00 sec)

MySQL [test4]> insert into ft values(5,'eeeee','efghi');
Query OK, 1 row affected (0.01 sec)

MySQL [test4]> select a, b, c from ft where match(b) against('bbbbb'
in boolean mode);
+---+-------+-------+
| a | b     | c     |
+---+-------+-------+
| 2 | bbbbb | bcdef |
+---+-------+-------+
1 row in set (0.01 sec)

MySQL [test4]> select a, b, c from ft where match(b) against('bbbbb'
in boolean mode) order by match(b) against('bbbbb' in boolean mode);
+---+-------+-------+
| a | b     | c     |
+---+-------+-------+
| 2 | bbbbb | bcdef |
+---+-------+-------+
1 row in set (0.01 sec)

◎
MySQL [test4]> select a, b, c from ft where match(b) against('bbbbb'
in boolean mode) order by match(c) against('bbbbb' in boolean mode);
Empty set (0.00 sec)

MySQL [test4]> select a, b, c from ft where match(c) against('bbbbb'
in boolean mode);
Empty set (0.01 sec)

MySQL [test4]> select a, b, c, match(b) against('bbbbb' in boolean
mode), match(c) against('bbbbb' in boolean mode) from ft where
match(b) against('bbbbb' in boolean mode) order by match(c)
against('bbbbb' in boolean mode);
Empty set (0.01 sec)

◎
MySQL [test4]> select a, b, c, match(b) against('bbbbb' in boolean
mode), match(c) against('bbbbb' in boolean mode) from ft where
match(b) against('bbbbb' in boolean mode);
Empty set (0.01 sec)

どうぞ、よろしくお願いいたします。

Groonga - fulltext search engine.

[groonga-dev,00986] Re: mroonga検索スコア順のソートについて