Bug #1422

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

Added by Kentoku SHIBA over 5 years ago. Updated over 5 years ago.

Status:完了チェック待ちStart date:07/06/2012
Priority:NormalDue date:
Assignee:Kentoku SHIBA% Done:

80%

Category:-
Target version:-

Description

連投になってしまい恐縮ですが、全く別件での質問です。
今回は、検索スコアを用いたソート方法について、教えていいただきたいことがあります。

以下、具体例で説明させていただきます。

【テーブル】
id・title・atuthor・publisherの4つのカラムを持つ、bookというテーブルを使用。


【やりたいこと】
title・author・publisherカラムをキーワード検索し、マッチするデータを取得する。
その際、検索結果を以下の順にソートする。
(1)titleの検索スコア
(2)authorの検索スコア
(3)publisherの検索スコア


【現在の状況】
上記の検索結果を得るために、まず最初に以下のSQLを実行しました。

(a)
SELECT * FROM book WHERE MATCH(title,author,publisher) AGAINST("キーワード"
in boolean mode) ORDER BY MATCH(title) AGAINST("キーワード" in boolean
mode) , MATCH(author) AGAINST("キーワード" in boolean mode) ,
MATCH(publisher) AGAINST("キーワード" in boolean mode);

これでSQLは通ったのですが、得られた結果が、
ORDER BY句をつけないときと比べ、明らかに減少してしまいました。
これは恐らく、WHERE句に使用していない条件を、
ORDER BY句に使用したのが原因だと考えられたので、続いて(b)を実行しました。

(b)
SELECT * FROM book WHERE MATCH(title) AGAINST("キーワード" in boolean mode)
OR MATCH(author) AGAINST("キーワード" in boolean mode) OR MATCH(publisher)
AGAINST("キーワード" in boolean mode) ORDER BY MATCH(title) AGAINST("キーワード"
in boolean mode) , MATCH(author) AGAINST("キーワード" in boolean mode) ,
MATCH(publisher) AGAINST("キーワード" in boolean mode);

こちらで、目的の結果を得ることが出来ました。
ただし、(b)のSQLは冗長で、パフォーマンス的にもあまり良くないように思われます。


【質問】
(1)目的の検索結果を取得するために、(b)以外で、より効率的なSQL文を書くことは可能でしょうか。
  ((a)のように、マルチカラムインデックスを使う方法で実現出来るとうれしいのですが・・・。)
(2)mroongaをパフォーマンスチューニングするための設定ファイルなどは存在するのでしょうか。

History

#1 Updated by Kentoku SHIBA over 5 years ago

> 【テーブル】
> id・title・atuthor・publisherの4つのカラムを持つ、bookというテーブルを使用。

もしよかったらテーブル定義(create table (...))を教えて頂い
てもよいでしょうか?インデックスの張り方を確認したいのと、手
元でも試してみたいというのが理由です。

> (a)
> SELECT * FROM book WHERE MATCH(title,author,publisher) AGAINST("キーワード"
> in boolean mode) ORDER BY MATCH(title) AGAINST("キーワード" in boolean
> mode) , MATCH(author) AGAINST("キーワード" in boolean mode) ,
> MATCH(publisher) AGAINST("キーワード" in boolean mode);
>
> これでSQLは通ったのですが、得られた結果が、
> ORDER BY句をつけないときと比べ、明らかに減少してしまいました。
> これは恐らく、WHERE句に使用していない条件を、
> ORDER BY句に使用したのが原因だと考えられたので、続いて(b)を実行しました。

うーん、ORDER BYをつけたかどうかで結果が少なくなることはない
と思うんですよねぇ。。。どうしてかしら。
できれば手元でも試してみたいです。


> (b)
> SELECT * FROM book WHERE MATCH(title) AGAINST("キーワード" in boolean mode)
> OR MATCH(author) AGAINST("キーワード" in boolean mode) OR MATCH(publisher)
> AGAINST("キーワード" in boolean mode) ORDER BY MATCH(title) AGAINST("キーワード"
> in boolean mode) , MATCH(author) AGAINST("キーワード" in boolean mode) ,
> MATCH(publisher) AGAINST("キーワード" in boolean mode);
>
> こちらで、目的の結果を得ることが出来ました。
> ただし、(b)のSQLは冗長で、パフォーマンス的にもあまり良くないように思われます。

たしかに、検索回数が増えますね。
マルチカラムインデックスでは1回の検索で済んだものが3回の検索
になります。

が、ORDER BYでそれぞれのカラム毎のスコアを使いたいというので
あれば、どうせ3回検索しなければいけないので、それを踏まえると
パフォーマンス的にはむしろ(b)の方が(a)よりよさそうな気がしま
す。

というのは、同じSQL内に同じMATCH AGAINSTが複数あっても実際に
は1度しか検索しないからです。(a)では以下のように4つのMATCH
AGAINSTがあり、すべて違うので4回検索します。

  * MATCH(title,author,publisher) AGAINST("キーワード" in boolean mode)
  * MATCH(title) AGAINST("キーワード" in boolean mode)
  * MATCH(author) AGAINST("キーワード" in boolean mode)
  * MATCH(publisher) AGAINST("キーワード" in boolean mode)

一方、(b)は以下のように6つのMATCH AGAINSTがありますが、重複
を除くと3種類になるので、3回しか検索しません。

  * MATCH(title) AGAINST("キーワード" in boolean mode)
  * MATCH(author) AGAINST("キーワード" in boolean mode)
  * MATCH(publisher) AGAINST("キーワード" in boolean mode)
  * MATCH(title) AGAINST("キーワード" in boolean mode)
  * MATCH(author) AGAINST("キーワード" in boolean mode)
  * MATCH(publisher) AGAINST("キーワード" in boolean mode)

よって、(b)の方がパフォーマンスがよさそうです。

> 【質問】
> (1)目的の検索結果を取得するために、(b)以外で、より効率的なSQL文を書くことは可能でしょうか。
>   ((a)のように、マルチカラムインデックスを使う方法で実現出来るとうれしいのですが・・・。)

冗長ですがORDER BYの数を減らせないということのようなので、
(b)が一番効率がよさそうです。

> (2)mroongaをパフォーマンスチューニングするための設定ファイルなどは存在するのでしょうか。

何かのバッファサイズを指定する、とかそういう類の設定というこ
とですよね。実は、そういうのは存在しないのです。今後、できる
かもしれませんが、今のところはありません。

#2 Updated by Kentoku SHIBA over 5 years ago

>パフォーマンス的にはむしろ(b)の方が(a)よりよさそうな気がします。
>
>というのは、同じSQL内に同じMATCH AGAINSTが複数あっても実際に
>は1度しか検索しないからです。
>
>冗長ですがORDER BYの数を減らせないということのようなので、
>(b)が一番効率がよさそうです。

ご説明ありがとうございます。
なるほど。重複を除いたMATCH AGAINSTの数が重要なのですね。
それでは(b)の方向で、なるべくMATCH AGAINSTが少なくなるような設計を考えてみます。
ありがとうございました!


>何かのバッファサイズを指定する、とかそういう類の設定というこ
>とですよね。実は、そういうのは存在しないのです。今後、できる
>かもしれませんが、今のところはありません。

こちらも了解致しました。
やはり検索回数を減らす方法で、パフォーマンスを向上させたいと思います。


>もしよかったらテーブル定義(create table (...))を教えて頂い
>てもよいでしょうか?インデックスの張り方を確認したいのと、手
>元でも試してみたいというのが理由です。

以下にテーブル構造を貼ります。
※実際に使用していたbookテーブルは、先程お伝えしたものより複雑な構造のため、
book2テーブルで再現してみました。
こちらでも同じ検索結果が得られました。

mysql> show create table book2 \G
*************************** 1. row ***************************
       Table: book2
Create Table: CREATE TABLE `book2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(254) COLLATE utf8_unicode_ci DEFAULT NULL,
  `author` varchar(254) COLLATE utf8_unicode_ci DEFAULT NULL,
  `publisher` varchar(254) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `book_title` (`title`),
  KEY `book_title_author` (`title`,`author`),
  FULLTEXT KEY `full_book_title` (`title`),
  FULLTEXT KEY `full_book_title_author` (`title`,`author`),
  FULLTEXT KEY `full_book_author` (`author`),
  FULLTEXT KEY `full_book_publisher` (`publisher`),
  FULLTEXT KEY `full_book_title_author_publisher` (`title`,`author`,`publisher`)
) ENGINE=mroonga DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

こちらには、約20万件のデータが入っています。
mysql> select count(*) from book2;
+----------+
| count(*) |
+----------+
|   190818 |
+----------+
1 row in set (0.00 sec)

参考までに、以下、実行したSQLと取得した結果の件数です。
(1)SELECT * FROM book2 WHERE MATCH(title,author,publisher)
AGAINST("日本" in boolean mode);
⇒15500件
(2)SELECT * FROM book2 WHERE MATCH(title,author,publisher)
AGAINST("日本" in boolean mode) ORDER BY MATCH(title,author,publisher)
AGAINST("日本" in boolean mode);
⇒15500件
(3)SELECT * FROM book2 WHERE MATCH(title,author,publisher)
AGAINST("日本" in boolean mode) ORDER BY MATCH(title) AGAINST("日本" in
boolean mode);
⇒7568件の結果
(4)SELECT * FROM book2 WHERE MATCH(title,author,publisher)
AGAINST("日本" in boolean mode) ORDER BY MATCH(author) AGAINST("日本" in
boolean mode);
⇒1762件の結果
(5)SELECT * FROM book2 WHERE MATCH(title,author,publisher)
AGAINST("日本" in boolean mode) ORDER BY MATCH(publisher) AGAINST("日本"
in boolean mode);
⇒7204件の結果
(6)SELECT * FROM book2 WHERE MATCH(title,author,publisher)
AGAINST("日本" in boolean mode) ORDER BY MATCH(title) AGAINST("日本" in
boolean mode),MATCH(author) AGAINST("日本" in boolean mode);
⇒228件の結果
(7)SELECT * FROM book2 WHERE MATCH(title,author,publisher)
AGAINST("日本" in boolean mode) ORDER BY MATCH(title) AGAINST("日本" in
boolean mode),MATCH(author) AGAINST("日本" in boolean
mode),MATCH(publisher) AGAINST("日本" in boolean mode);
⇒31件の結果
(8)SELECT * FROM book2 WHERE MATCH(title) AGAINST("日本" in boolean mode);
⇒7568件の結果

今試していてわかったのですが、ORDER BY句を指定すると、
そのカラムにキーワードが含まれていないデータは除外されているようです。
例えば(7)では、title・author・publisherの全てに"日本"が含まれているものしかマッチしていないと思われます。
またその証拠に、(3)と(8)では同じ検索結果が得られています。

テーブル構造などで、何か問題になりそうな箇所がありましたら、
ご指摘いただけますと幸いです。

#3 Updated by Kentoku SHIBA over 5 years ago

  • Assignee set to Kentoku SHIBA
  • % Done changed from 0 to 20

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)

#4 Updated by Kentoku SHIBA over 5 years ago

  • Status changed from 新規 to 完了チェック待ち
  • % Done changed from 20 to 80

フラグなどでは判断できないようだったので、 各ft_init_ext()呼び出しのitemオブジェクトを特定した後、 それがwhere句に含まれるかどうかを確認する方式で実装した。

Also available in: Atom PDF