Mroonga

> 【テーブル】
> id・title・atuthor・publisherの４つのカラムを持つ、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をパフォーマンスチューニングするための設定ファイルなどは存在するのでしょうか。

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

>パフォーマンス的にはむしろ(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)では同じ検索結果が得られています。

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