MySQL query WHERE MATCH not giving results I need

[radiskull852]

Hey guys,

I am new to this forum, but I was hoping you would have an answer to my question.

I have a database that I am searching using a MySQL SELECT query with the MATCH AGAINST syntax. My query looks something like this:

SELECT * FROM products WHERE MATCH (description) AGAINST ("user query goes here" IN BOOLEAN MODE)

As you can tell, its a products database for an online store. If a user searches for "TV" in the query, nothing shows up because the WHERE MATCH syntax will ignore anything less than 4 characters. It's not good when user's can't find a TV.

Are there any better search algorithms in MySQL? It seems like this one really sucks.

Please reply with any good algorithm for searching through a MySQL database. I'll try anything.

Thanks,
Bryan

[vinilios]

the limit is taken from the sql system variable "ft_min_word_len" (theres also a ft_max_word_len variable for the maximum match characters) wich can be changed with the following command via SQL

SET ft_min_word_len = <minimum word length>;

or from command line

mysqld --set-variable=ft_min_word_len=<minimum word length>

(i think you need root rights on the sql service to change variables though).

also even if you fix that you should consider the "stopwords" wich are words that appear many times in the match data.

more info can be @

FULLTEXT SEARCH [dev.mysql.com]
TUNING SEARCH [dev.mysql.com]
CHANGING SYSTEM VARS [dev.mysql.com]