MySQL full text search (php)

Question

Hi I'm trying to create a search box to search customer by last name and/or first name and/or phone number.

Here is my SQL Query:

SELECT * 
  FROM customer 
 WHERE Match(c_fname,c_lname,c_phone) Against('$keywords')

My questions is Why does it work when i search for bill and not if i search for bob?

Another question is how do i prioritize the fields(last name most important), so that if i search for John Smith and output is Will smith, Rob Smith, John Clinton, and John Smith, John Smith would be on very top.

Answer 1

The MysQL full text search engine will exclude words of 3 letters or less from the full text search by default.

From the MySQL Manual:

The minimum and maximum lengths of words to be indexed are defined by the ft_min_word_len and ft_max_word_len system variables. (See Section 5.1.4, “Server System Variables”.) The default minimum value is four characters; the default maximum is version dependent.

Additionally, words that are present in 50% or more of the rows in the table are considered common and do not match. Neither do stopwords which the system defines as common words. It is not possible to increase this 50% threshold for standard full text searches without recompiling MySQL. Instead, you may want to consider looking at using Boolean Mode Full Text Searches which do not have this 50% row issue.

Details of Boolean mode full text searches can be found in the Boolean Full-Text Searches section of the MySQL manual.

The list of default stopwords in on the MySql website.