0

I have a situation with a website who lists products.

I'm using a fulltext index with a Match Against search

There is a field named "itemcode" that field is in varchar type

turns out one of the codes for instance, goes like

"WTC-153-U"

And when i type on the textbox "WTC-153" i get no results, i guess it's based on the hyphen and numbers, is there any workaround or a better method for this?

I cannot seem to find a solution at the moment without having to modify that much code

The query goes like this

SELECT field FROM table WHERE MATCH(itemcode) AGAINST ('WTC-153')

Thanks

DJ22T
  • 1,628
  • 3
  • 34
  • 66
  • What does your query look like? I hope you are using `where itemcode like 'WTC-153%'`? – TJ- Aug 18 '14 at 20:32
  • `Like` didn't ever worked for me, since when they were mixed words no matches were found, im using match against – DJ22T Aug 18 '14 at 20:35
  • @TJ- the whole point of MySQL FULLTEXT is to avoid expensive `LIKE` queries. – lxg Aug 18 '14 at 20:52

1 Answers1

0

- counts as a word separator, so 'WTC-153-U' counts as three words, 'WTC', '153' and 'U'.

With MyISAM, the default lower word length limit for full text searches is four characters and with InnoDB it is 3 (these can be changed).

If you are using InnoDB, you could get around the issue by searching for both 'WTC' and '153' in the text.. obviously, this will return a result even if these words are not together or in the right order.

Otherwise you could search with LIKE '%WTC-153%' but that wouldn't be able to use the index. LIKE 'WTC-153%' will be able to use the index, but will only return the row if the field starts with 'WTC-153'.

Arth
  • 12,789
  • 5
  • 37
  • 69
  • Hi, i'm using MyISAM, the MySQL version won't allow fullindex with InnoDB, how can i change the length? and what work around can i use which doesn't affect the current code so much, since it based on a bunch of dynamic filters and joins – DJ22T Aug 18 '14 at 20:40
  • @DannyG I'd use `LIKE '%WTC-153-U%'`, it is the only thing guaranteed to return the correct results. On further inspection it is very strange to have a full text index on an itemcode. – Arth Aug 18 '14 at 20:42
  • i tought about that, but what if there is an item named "Black stripped keyboard" and the user types "black keyboard"? – DJ22T Aug 18 '14 at 20:45
  • You should really separate out your itemcode search logic from your item name search logic.. `LIKE` is case insensitive however. – Arth Aug 18 '14 at 20:48