MYSql Full Text Boolean search and relevancy score improvements

full-text-searchMySQL

I am using a mysql BOOLEAN full text search as shown in the query below and am getting what I think to be bad results that could be more relevant.

Query:

SELECT phppos_items.name,MATCH (phppos_items.name) 
AGAINST ('Samsung S4 LCD*' IN BOOLEAN MODE) as rel 
FROM `phppos_items` 
WHERE MATCH (phppos_items.name) AGAINST ('Samsung S4 LCD*' IN BOOLEAN MODE)
ORDER BY `rel` DESC LIMIT 20;

The search is Samsung S4 LCD* IN BOOLEAN MODE and I would think "Samsung S4 LCD/Digi Black" and "Samsung S4 LCD/Digi White" would have a higher relevancy score; but it does NOT (Shown below).

Many of the items in the database contain 2 characters or less to search.

So I changed ft_min_word_len to 2 and restarted server and then dropped and created database again. I thought this would help; but it did not.

Do you have any idea on how to fix this? I need to use boolean mode as Natural Language search has the 50% rule and becoming a STOP word.

Example queries/results

mysql> show global variables WHERE Variable_name = 'ft_min_word_len';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| ft_min_word_len | 2     |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> use pos;                                                                
Database changed
mysql> SELECT phppos_items.name,MATCH (phppos_items.name) AGAINST ('Samsung S4 LCD*' IN BOOLEAN MODE) as rel FROM `phppos_items` WHERE MATCH (phppos_items.name) AGAINST ('Samsung S4 LCD*' IN BOOLEAN MODE) ORDER BY `rel` DESC LIMIT 20;
+----------------------------------+--------------------+
| name                             | rel                |
+----------------------------------+--------------------+
| Samsung Tab 3 8.0" T310 LCD/Digi | 1.9734089374542236 |
| Samsung Note 10.1" LCD/Digi      | 1.9734089374542236 |
| Samsung Tab 3 Lite 7" T110 LCD   | 1.9734089374542236 |
| Samsung Tab 4 7" T230 LCD        | 1.9734089374542236 |
| Samsung Tablet P600 LCD Black    | 1.9734089374542236 |
| Samsung T800/805 LCD/Digi        | 1.9734089374542236 |
| Samsung S3 LCD/Digi Blue         | 1.9734089374542236 |
| Samsung S3 LCD/Digi White        | 1.9734089374542236 |
| Samsung S4 LCD/Digi Black        | 1.9734089374542236 |
| Samsung S4 LCD/Digi White        | 1.9734089374542236 |
| Samsung S5 LCD/Digi Black        | 1.9734089374542236 |
| Samsung S5 LCD/Digi White        | 1.9734089374542236 |
| Samsung S4 Mini LCD/Digi Black   | 1.9734089374542236 |
| Samsung S4 Mini LCD/Digi White   | 1.9734089374542236 |
| Samsung i9000 LCD/Digi           | 1.9734089374542236 |
| Samsung Note 2 LCD/Digi Black    | 1.9734089374542236 |
| Samsung Note 2 LCD/Digi White    | 1.9734089374542236 |
| Samsung Note 3 LCD/Digi Black    | 1.9734089374542236 |
| Samsung Note 3 LCD/Digi White    | 1.9734089374542236 |
| Samsung Ace 2X LCD               | 1.9734089374542236 |
+----------------------------------+--------------------+
20 rows in set (0.00 sec)

mysql> SELECT @@VERSION;
+-----------+
| @@VERSION |
+-----------+
| 5.6.26    |
+-----------+
1 row in set (0.00 sec)

Best Answer

The problem was: ft_min_word_len is only for MyIsam and the correct variable is innodb_ft_min_token_size