why spanish word not search properly in full text index with exacet match

Question

I have use full text index in my food search and in a food table records are multilingual so i have search with "jamón" (Spanish language) food but it's search end of list and not showing first and show other English language(jamon food) food in top of list so how can i search exact match first then other without take too much time in search query My code like this

$food_data = Nutrition::whereRaw("MATCH(brand_with_name) AGAINST (? IN NATURAL LANGUAGE MODE)", [$item])
            ->with('guNutrition', 'esNutrition')
            ->limit($per_page)
            ->offset($off_set)
            ->get();

I have use Laravel and Mysql for my app.
I try with following code but it take too much time and not working proper(not showing exact match first then other) with Spanish. only working proper with English

$food_data = Nutrition::whereRaw("MATCH(brand_with_name) AGAINST (? IN NATURAL LANGUAGE MODE)", [$item])
                            ->select('*',\DB::raw('CASE WHEN brand_with_name = "' . $item . '" THEN 1 ELSE 0 END AS score'),\DB::raw('MATCH(brand_with_name) AGAINST ("' . $item . '") AS score2'))
                            ->with('guNutrition', 'esNutrition')
                            ->orderBy('score', 'DESC')
                            ->orderBy('score2', 'DESC')
                            ->limit($per_page)
                            ->offset($off_set)
                            ->get();

score 1 · Answer 1 · answered Jun 09 '17 at 05:34

The issue is because of inappropriate Character sets & collations. If your searches on that field are always going to be accent-sensitive, then declare the collation of the field as utf8_bin (that'll compare for equality the utf8-encoded bytes) or use a language specific collation that distinguish between the accented and un-accented characters.

In my version (MySql 5.0), there is not available any utf8 charset collate for case insensitive, accent sensitive searches. The only accent sensitive collate for utf8 is utf8_bin. However it is also case sensitive.

Ex:

SELECT * FROM `words` WHERE LOWER(column) = LOWER('aBád') COLLATE utf8_bin

Reference 1

Reference 2

why spanish word not search properly in full text index with exacet match

1 Answers1