mysqlmatch-against

MySQL match against 0 result in MyISAM but works on InnoDB

i have this table

CREATE TABLE `tbl` (
  `id` bigint(20) NOT NULL,
  `code` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `tbl` ADD PRIMARY KEY (`id`);
ALTER TABLE `tbl` ADD FULLTEXT KEY `code` (`code`);

INSERT INTO `tbl` (`id`, `code`) VALUES (1, '110.103');

and this is my query

SELECT code FROM `tbl` WHERE (MATCH (code) AGAINST ('110.103*' IN BOOLEAN MODE) )

but there is no result. any idea?

Solution

  • First of all, a fulltext indexes stores words. Words are seperated e.g. by a period - actually anything that isn't a letter, a digit, an underscore or a single apostrophe. So your text currently consists of two words, "101" and "103".

    The MySQL fulltext index will only store words with a minimum word length. MyISAM uses 4 as the default value for ft_min_word_len, so your 3-digit "words" are not included, while InnodB uses 3 for innodb_ft_min_token_size, which is why they are included. Change these settings (and rebuild your index), and both engines will find the same rows.

    That being said: MATCH (code) AGAINST ('110.103*' IN BOOLEAN MODE) will still search for words. It will find any row that contains the "word" 110 or(!) a word starting with 103. You can enforce to have both words with ('+110 +103*' IN BOOLEAN MODE), but they can be in any order. You can enforce a specific order if you use against('"110 103"'), but " does not support * anymore.

    A common trick is to store your numbers with an underscore 110_103 (and search for it that way), then this is a complete word. You can e.g. do this by adding a code_search-column that you keep up-to-date using a trigger (or define it as a generated column), and search in that column. If your code can contain _ too, it does not distinguish between _ and . anymore, so you have to add an additional condition (... where match (code_search) against (...) and code like '...') or something like that).

    But I am not sure if you actually need a fulltext search here. If your column actually contains only a single code (and not a text with several seperate words, which is the use case for a fulltext index), you might want to try where code like '101.103%', where code like '%101.103%' or where code like '101.103%' or code like '% 101.103%' depending on what you are looking for exactly. Add a (normal) index for code for this, and at least the first search will be even faster than the fulltext search (apart from the fact that it will, in contrast to the fulltext search, give you the reqired result).

    And although I doubt it is the case here: if you are actually looking for floating point numbers, you will make your life a lot easier if you can store them as actual numbers in a seperate column (or a seperate table if the data contains more than one floating point number per row).