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)
