MySQL Full-text search - search for short words

Question

I have a problem. I made a simple search engine which searches by brand and model of car. For reasons of query performance and a lot of data in database, I decided to use full-text search. It's ok, but now I come across the problem:

I would like to find all cars with brand "Audi" and with model "Q7". For now, I have this SQL query, but it doesn't work right, because of word length "Q7":

SELECT `a`.`id`, `a`.`title`, `a`.`askprice`, `a`.`description`, `a`.`picture`
FROM (`mm_ads` as a)
WHERE `a`.`category` =  '227'
AND `a`.`askprice` >= '0'
AND `a`.`askprice` <= '144000'
AND (MATCH(a.title) AGAINST ('+audi +q7' IN BOOLEAN MODE ))
GROUP BY `a`.`id`
ORDER BY `a`.`id` ASC
LIMIT 30

I don't have access to modify MySQL config file, to set ft_min_word_len to value 2. For now value is 3. Is there any other way to deal with that?

Here is another problem:

I would like to get all cars brand "BMW" and model "116". For example, I have a car named BMW, 1, 116i. My SQL query is:

`SELECT `a`.`id`, `a`.`title`, `a`.`askprice`, `a`.`description`, `a`.`picture`
FROM (`mm_ads` as a)
WHERE `a`.`category` =  '227'
AND `a`.`askprice` >= '0'
AND `a`.`askprice` <= '144000'
AND (MATCH(a.title) AGAINST ('+bmw +116' IN BOOLEAN MODE))
GROUP BY `a`.`id`
ORDER BY `a`.`id` ASC
LIMIT 30`

Search return 0 rows. Why? All input strings ("BMW", "116") are min length 3. What am I doing wrong?

Regards, Mario

you will have to use non full text searching for the smaller words. — user557846
From what I've seen, it isn't possible without editing the ini file. On top of that, this isn't a very good solution for the type of searching you're doing. I'd recommend just using something similar to LIKE ('*bmw*') AND LIKE ('*116*'). It would return very similar results, and allow a lot more flexibility. Though fulltext is a much simpler solution, it definitely isn't the best fit. — Ben
@mazzzzz I understand. LIKE is in this case much more flexibility. But, is faster? I really need to consider query performance.. — Puzo
They should be about the same performance, but if you are worried about it, why not make your own keyword table — Ben

Kai Qing Kai Qing · Accepted Answer · 2011-11-28T20:12:50

I had a similar issue when dealing with match against (regarding text length) and my answer was to strlen the string first and switch between like and match against for shorter words. Not what I would call graceful, but it was all I could do since I too had no access to the config.

As for the second question, are you sure the default isn't 4? I recall I couldn't search on the term "art" in my case. 3 letters. Had to go with like on everything below 4 chars.

MySQL Full-text search - search for short words

3 Answers