CODEWITHSUNDEEP
mysqlmariadb
Sandeep
Sandeep

Reputation: 1401

MySQL (Windows10) FULLTEXT searching with MyISAM tables not working

I have a problem which I have been able to recreate with two very simple tables. The tables were defined as follows:

create table Temp_Table_MyISAM(  
  id  INT UNSIGNED AUTO_INCREMENT,  
  code VARCHAR(10) NOT NULL,  
  name VARCHAR(256) NOT NULL,    
  PRIMARY KEY (id),  
  KEY (code),  
  FULLTEXT (name)  
) ENGINE = MYISAM;  


create table Temp_Table_InnoDB(  
  id  INT UNSIGNED AUTO_INCREMENT,  
  code VARCHAR(10) NOT NULL,  
  name VARCHAR(256) NOT NULL,  
  PRIMARY KEY (id),  
  KEY (code),  
  FULLTEXT (name)  
);

Each table has two rows, as can be seen from the result of the following two queries:

select * from Temp_Table_MyISAM; 

 +----+---------+----------------+  
 | id | code    | name           |  
 +----+---------+----------------+  
 |  1 | AC-7865 | 38 NORTHRIDGE  |  
 |  2 | DE-3514 | POLARIS VENTRI |  
 +----+---------+----------------+

select * from Temp_Table_InnoDB; 

 +----+---------+----------------+  
 | id | code    | name           |  
 +----+---------+----------------+  
 |  1 | AC-7865 | 38 NORTHRIDGE  |  
 |  2 | DE-3514 | POLARIS VENTRI |  
 +----+---------+----------------+

When I do a FULLTEXT search on the MyISAM table, I don't get any hits

MariaDB [stackoverflow]> SELECT name, code FROM Temp_Table_MyISAM WHERE MATCH(name) AGAINST('38');
Empty set (0.00 sec)

MariaDB [stackoverflow]> SELECT name, code FROM Temp_Table_MyISAM WHERE MATCH(name) AGAINST('POLARIS');
Empty set (0.00 sec)

When I do a FULLTEXT search on the InnoDB table, I get a hit only when the pattern to be matched does not start with a numeric value

MariaDB [stackoverflow]> SELECT name, code FROM Temp_Table_InnoDB WHERE MATCH(name) AGAINST('38');
Empty set (0.00 sec)

MariaDB [stackoverflow]> SELECT name, code FROM Temp_Table_InnoDB WHERE MATCH(name) AGAINST('POLARIS');

+----------------+---------+  
| name           | code    |  
+----------------+---------+  
| POLARIS VENTRI | DE-3514 |  
+----------------+---------+

Any insight would be appreciated.

Upvotes: 0

Views: 163

Answers (1)

Rick James
Rick James

Reputation: 142356

There are 3 rules to watch out for in MyISAM's FULLTEXT:

  • Text words shorter than ft_min_word_len (default 4 characters) will not be indexed. ("38")

  • Search words that show up in more 50% or more of the rows, will be ignored. ("Polaris")

  • "Stop words" in the text are not indexed. ("the", "and", ...)

Since InnoDB now supports FULLTEXT, you should move to that engine. (And the rules are different there.)

Upvotes: 2

Related Questions