If I index the terms Maya Angelou and May Angelou (using MySQL's ngram fulltext index parser) and then search for the term may, the two items get the exact same score. Is there a way to optimize it so that May Angelou comes up first?
See:
CREATE TABLE test(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200)
);
ALTER TABLE test ADD FULLTEXT INDEX `test_fulltext_index`(title) WITH PARSER ngram;
insert into test(title) values('Maya Angelou');
insert into test(title) values('May Angelou');
SELECT title, MATCH(title) AGAINST ('may') as SCORE FROM test WHERE MATCH(title) AGAINST ('may');
This results in:
+--------------+----------------------------+
| title | SCORE |
+--------------+----------------------------+
| Maya Angelou | 0.000000003771856604828372 |
| May Angelou | 0.000000003771856604828372 |
+--------------+----------------------------+
Of course, using a normal FULLTEXT index (without the ngram parser) gives the following:
+-------------+--------------------+
| title | SCORE |
+-------------+--------------------+
| May Angelou | 0.0906190574169159 |
+-------------+--------------------+
I thought of having two indexes: one a normal FULLTEXT index and the other one using WITH PARSER ngram and then combining the results, but this doesn't seem to be possible.
How could this be done?
