MySQL MATCH doesn't work with two characters?

I have a table called 'business' with the following sample data:

Street - 150 N Michigan Ave.
City - Chicago
State - IL
Zip - 60601

When I run a query like

SELECT business.* 
    WHERE MATCH(business.Street, business.City, business.State, business.Zip)
                AGAINST('*150*' IN BOOLEAN MODE)
-- IT WORKS


SELECT busi开发者_运维百科ness.* 
    WHERE MATCH(business.Street, business.City, business.State, business.Zip)
                AGAINST('*Chicago*' IN BOOLEAN MODE)
-- IT WORKS


SELECT business.* 
    WHERE MATCH(business.Street, business.City, business.State, business.Zip)
                AGAINST('*60601*' IN BOOLEAN MODE)
-- IT WORKS


SELECT business.* 
    WHERE MATCH(business.Street, business.City, business.State, business.Zip)
                AGAINST('*IL*' IN BOOLEAN MODE)
-- DOESNT WORK!!

So what's wrong with the last query?

Any ideas?

---

Update the ft_min_word_len variable in the my.cnf MySQL configuration file:

[mysqld]
ft_min_word_len=N

Note that afterwards indexes must be rebuilt.

---

Fulltext search stop some common word, but it is disable using this steps.

1: open my.ini file in MYSQL configuration file.

2: place ft_min_word_len=1 line after [mysqld] line in my.ini

[mysqld]
ft_min_word_len=1

3: restart your server

4: repair your table using below command

repair table tablename;

5: now your search is working....

---

Looks like you're running into the minimum length limit, as stated in the MySQL docs.

---

For future references. The answers above are related to myisam engine (not innodb).

So, to fix the minimum length of words for innodb you need innodb_ft_min_token_size (default value: 3)

my.cnf:

[mysqld]
innodb_ft_min_token_size=2

Then rebuild the index (source):

alter table your_name drop index index_name;
alter table your_name add fulltext(field_name);

In order to check which engine you use execute the following query and see the Engine column:

show table status where `name` = 'table_name';

---

I tested both MyISAM and InnoDB.

The leading '*' seems to be ignored.

Because of the trailing '*', ft_min_word_len or innodb_min_token_size is ignored.

If your dataset is mostly for businesses in "IL", the following rule may kick in: "If more than 50% of the rows contain a word, that word is not indexed."

Also, don't forget about "stopwords".