Kazuhiko
kazuh****@fdiar*****
2014年 10月 24日 (金) 19:04:26 JST
かずひこです。
On 24/10/2014 11:44, Naoya Murakami wrote:
> Whereで絞り込んでないからおかしく感じるのでは？と思いました。
> Whereで絞り込めばスコア0のはでてこないです。
+wordの順序によって、wordが入っていないレコードも非ゼロのスコアになっ
て、WHEREで指定してもヒットしてしまいます。
> mysql> SELECT title, MATCH (title) AGAINST('fox dog +quick' IN BOOLEAN
> MODE) AS
> -> score FROM books WHERE MATCH (title) AGAINST('fox dog +quick' IN
> BOOLEAN MODE);
> +------------------------------------------+-------+
> | title | score |
> +------------------------------------------+-------+
> | Quick brown fox jumps over the lazy dog. | 3 |
> +------------------------------------------+-------+
> 1 row in set (0.00 sec)
>
> mysql> SELECT title, MATCH (title) AGAINST('fox dog +quick' IN BOOLEAN
> MODE) AS score FROM books;
> +---------------------------------------------+-------+
> | title | score |
> +---------------------------------------------+-------+
> | Quick brown fox jumps over the lazy dog. | 3 |
> | Slow white fox jumps over the diligent dog. | 0 |
> +---------------------------------------------+-------+
> 2 rows in set (0.00 sec)
最後が+wordだとそうなるのですが、そうでないと以下のようになります。
DROP TABLE IF EXISTS books;
CREATE TABLE books (
`id` INTEGER AUTO_INCREMENT,
`title` text,
PRIMARY KEY(`id`),
FULLTEXT INDEX title_index (title)
) ENGINE=mroonga default charset utf8;
INSERT INTO books (title) VALUES (
'Quick brown fox jumps over the lazy dog.'
);
INSERT INTO books (title) VALUES (
'Slow white fox jumps over the diligent dog.'
);
SELECT title, MATCH (title) AGAINST('+quick fox dog' IN BOOLEAN MODE) AS
score FROM books;
+---------------------------------------------+-------+
| title | score |
+---------------------------------------------+-------+
| Quick brown fox jumps over the lazy dog. | 3 |
| Slow white fox jumps over the diligent dog. | 2 |
+---------------------------------------------+-------+
2 rows in set (0.00 sec)
SELECT title, MATCH (title) AGAINST('+quick fox dog' IN BOOLEAN MODE) AS
score FROM books WHERE MATCH (title) AGAINST('+quick fox dog' IN BOOLEAN
MODE);
+---------------------------------------------+-------+
| title | score |
+---------------------------------------------+-------+
| Quick brown fox jumps over the lazy dog. | 3 |
| Slow white fox jumps over the diligent dog. | 2 |
+---------------------------------------------+-------+
2 rows in set (0.00 sec)
SELECT title, MATCH (title) AGAINST('fox +quick dog' IN BOOLEAN MODE) AS
score FROM books;
+---------------------------------------------+-------+
| title | score |
+---------------------------------------------+-------+
| Quick brown fox jumps over the lazy dog. | 3 |
| Slow white fox jumps over the diligent dog. | 1 |
+---------------------------------------------+-------+
2 rows in set (0.00 sec)
SELECT title, MATCH (title) AGAINST('fox +quick dog' IN BOOLEAN MODE) AS
score FROM books WHERE MATCH (title) AGAINST('fox +quick dog' IN BOOLEAN
MODE);
+---------------------------------------------+-------+
| title | score |
+---------------------------------------------+-------+
| Quick brown fox jumps over the lazy dog. | 3 |
| Slow white fox jumps over the diligent dog. | 1 |
+---------------------------------------------+-------+
2 rows in set (0.01 sec)
ちなみにMyISAM (ft_min_word_len=3) とInnoDB FTSだと以下のようになります
（もうひとつのレコードはスコアがゼロ）。
ALTER TABLE books engine=MyISAM;
SELECT title, MATCH (title) AGAINST('fox +quick dog' IN BOOLEAN MODE) AS
score FROM books WHERE MATCH (title) AGAINST('fox +quick dog' IN BOOLEAN
MODE);
+------------------------------------------+--------------------+
| title | score |
+------------------------------------------+--------------------+
| Quick brown fox jumps over the lazy dog. | 1.6666667461395264 |
+------------------------------------------+--------------------+
1 row in set (0.00 sec)
ALTER TABLE books engine=InnoDB;
SELECT title, MATCH (title) AGAINST('fox +quick dog' IN BOOLEAN MODE) AS
score FROM books WHERE MATCH (title) AGAINST('fox +quick dog' IN BOOLEAN
MODE);
+------------------------------------------+---------------------+
| title | score |
+------------------------------------------+---------------------+
| Quick brown fox jumps over the lazy dog. | 0.09061906486749649 |
+------------------------------------------+---------------------+
1 row in set (0.01 sec)
On 24/10/2014 11:38, Kouhei Sutou wrote:
> そういう人が言い出しにくくなるくらい断定的に話を進めるのはよ
> くないんじゃないかなぁと思いました。
ごめんなさい…
かずひこ