CODEWITHSUNDEEP
phpmysql
Shan Biswas
Shan Biswas

Reputation: 429

Mysql FullText index searching issue

I am using MySql FullText indexing to search data from database.

Here is the query

$search_input_text = 'the_string_to_be_search';
$searchArray = explode(" ", $search_input_text);
$query="SELECT * FROM car_details
        WHERE  MATCH (car_trim) AGAINST ('";
foreach ($searchArray as $word) {
    $query .= "+".$word."* ";
}
$query .= "' IN BOOLEAN MODE) LIMIT $start, $limit";

The query is executing fine but it has a bug, if you look at the column name you will find car_trim which is inside the MATCH() function. The column has only 3 different types of values in the database which are 'T5', 'T6' and 'T5 premier'.

When I type 'Premier' in the search bar and hit Enter, it fetches the results whose values contain the word 'Premier'. But when I type T5 or T6 , it returns an empty record. Please be sure that there are lots of records with car_trim='T5', car_trim='T6' or car_trim='T5 Premier'

I am not getting that what can be the problem with the strings T5 and T6.

Upvotes: 1

Views: 102

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269523

MySQL has two key parameters when using full text search (and a few other important ones). The key parameters are the minimum word length and the stop words list. In short, MySQL ignores words that are less than 3 or 4 characters (depending on the storage engine) or that are in the stop word list.

Your examples ("T5" and "T6") are too short -- based on the parameter defaults.

Some other configuration parameters might be of interest, such as the maximum word length and the characters that are valid for words.

You can change the parameters for full text indexing and re-build the index.

Here is a good place to start in understanding this.

Upvotes: 2

Related Questions