CODEWITHSUNDEEP
mysqlsearchindexingfull-text-search
Kunal Aggarwal
Kunal Aggarwal

Reputation: 301

Include Single letter in fulltext search mysql

I have a mysql backend which has the table named device. A column in the table title is where I need to search. The title column contains the names of some mobile phones. Here is a sample dataset in the table:

+----+----------------------+
| id | title                |
+----+----------------------+
|  1 | Apple iPhone 4       |
|  2 | Apple iPhone 4S      |
|  3 | Apple iPhone 5       |
|  4 | Apple iPhone 5S      |
|  5 | Apple iPhone 6       |
|  6 | Apple iPhone 6 Plus  |
|  7 | Apple iPhone 6S      |
|  8 | Apple iPhone 6S Plus |
+----+----------------------+

I created a FULLTEXT Index on this table using the following command:

CREATE FULLTEXT INDEX idx ON device(title);

When I search using the following MySQL Command:

SELECT title FROM device WHERE MATCH (title) AGAINST ('iPhone 6');

The top result is iPhone 4.

What I've observed is that, it is ignoring the single digit at the end. If I search for iPhone 6S, I get the correct row at the top. But searches like iPhone 4, iPhone 5, iPhone 6, all result in iPhone 4 at the top. How do I solve this?

Thanks in advance.

Upvotes: 3

Views: 1770

Answers (3)

Ben
Ben

Reputation: 377

I used LIKE in addition to MATCH to take advantage of FULLTEXT, but only get exact matches...

SELECT title FROM device WHERE MATCH (title) AGAINST ('iPhone 6') AND title LIKE 'iPhone 6*';

OR you could just use LIKE to put the exact matches first

SELECT title FROM device WHERE MATCH (title) AGAINST ('iPhone 6') ORDER BY title LIKE 'iPhone 6*' DESC;

Upvotes: 0

Shadow
Shadow

Reputation: 34232

Fulltext index, and therefore search is based on words, not patterns. There is a minimum length parameter that applies to words, any word below that length is not indexed, therefore is not considered as part of the weighting of the results. A single character word (6) is probably below the limit, therefore is discarded.

What you can do:

  1. Reduce the minimum length limit to 1, see mysql's documentation on fine tuning fulltext indexes:

The minimum and maximum lengths of words to be indexed are defined by the innodb_ft_min_token_size and innodb_ft_max_token_size for InnoDB search indexes, and ft_min_word_len and ft_max_word_len for MyISAM ones.

Further consideration:

 - Fulltext indexes have to be rebuilt after this change
 - Fulltext indexes may grow extremely big with this change
  1. Change your fulltext search to boolean mode and search for the exact phrase:

SELECT title FROM device WHERE MATCH (title) AGAINST ('"Iphone 6"' IN BOOLEAN MODE) ;

or

SELECT title FROM device WHERE MATCH (title) AGAINST ('Iphone 6*' IN BOOLEAN MODE) ;

  1. Dump fulltext search and just use like with pattern matching.

Upvotes: 3

Solorad
Solorad

Reputation: 914

Could you try query 

SELECT title FROM device WHERE MATCH (title) AGAINST ('iPhone 6*' IN BOOLEAN MODE);

It should find all rows that contain words such as “iPhone 6” and “iPhone 6S”.

Upvotes: 0

Related Questions