0

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)
  • Don't trust "relevance" scores very much. – Rick James Jun 26 '16 at 5:16
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.