1

I have a table of products, e.g. cars. I want users to be able to search using multiple terms, e.g. for autocompletion. LIKE %term% is way to slow, because it's huge table. In order to make it fast, I want to use MySQL's fulltext search. I use ft_min_word_lenght = 1.

Imagine the productname is "Lexus RX450H". Then two words are indexed: "Lexus" and "RX450H". 

MATCH (productname) AGAINST ('+lex* +rx45*' IN BOOLEAN MODE) ')

..will match this product, good.

but

MATCH (productname) AGAINST ('+lex* +450*' IN BOOLEAN MODE) ')

wont, because it's imperative to search on begin of words.

Users might know the number, but might forget the letters. I want the second query to match.

If product was: "Lexus RX 450 H", then the last query would have matched, problem solved.

What I could do is create a second column in the table and split all letters/numbers by putting a space in between them, e.g. using regexp. Then, the search should be done on this column, voila, problem solved.

However, the (big) table will become almost twice as big and I was wondering whether it is possible to tell MySQL to perform the indexing like described, without creating an extra column.

Dirk
  • 2,167
  • 2
  • 20
  • 29
  • Here is the similar question: http://stackoverflow.com/questions/10731187/mysql-regexp-usage-within-boolean-match-against But he thinks, it's not possible to combine regex with BOOLEN MODE – user4035 May 17 '13 at 00:48

1 Answers1

-1

It looks like the query is returning rows as it should.

MATCH (productname) AGAINST ('+lex* +450*' IN BOOLEAN MODE) ')

Essentially says match text that begins with 'lex' and begins with '450'. Your data matches on the 'lex' but not on the '450'. Remove the asterisk on the '+405' and I believe that will work for you.

MATCH (productname) AGAINST ('+lex* +450' IN BOOLEAN MODE) ')

should return 'Lexus RX 450 H' and 'Lexus RX450H'.

Kevin Whatever
  • 184
  • 7