mysql full text search with null columns

Question

I am trying to get a full text search on a mysql database. The problem is that some of the fields can be left null. For example I have a basic address table with columns

StreetLine1

StreetLine2

City

State

Zip

Now none of these are going to be required so any one of them could be null. When I do my full text search

SELECT *, 
(MATCH (StreetLine1, StreetLine2, City, State, Zip) 
AGAINST ('+{0}*' IN BOOLEAN MODE)) AS Relevance 
FROM Address 
HAVING Relevance > 0 
Order By Relevance Desc

{0} is just a placeholder

If none of the fields are null it works great. But if one of them is null the row is not returned.

I figured I should just create a view so I did but then I could not figure out how to add the full text index to that view

Any suggestions?

Answer 1

These fields are FAR too small for full text search to be what you want to do. Plus, you have to make your tables MyIsam, which is generally less suitable than InnoDb.

You want to use the LIKE operator here.

Answer 2

Make sure you have, under [mysqld] in your my.cnf , a setting lower than the default 4 if you are trying to match things like street numbers or abbreviations like 'Ln', etc:

ft_min_word_len=2

I have used this for geographical data, where users want to search for 2 letter state abbreviations, etc -- it is disorienting the first time you realize that short words are absent from the index. Of course, you have to rebuild the indexes after changing the ft_min_word_len value.