MySQL Forums
Forum List  »  Newbie

Re: broad match search results, splitting the search by spaces
Posted by: Rick James
Date: November 12, 2014 11:03AM

First, some reminders about FULLTEXT.
* It works with "words".
* "Short" words are ignored. (see below). Hence, "21" is probably ignored.
* MyISAM and InnoDB each have FULLTEXT indexing, but they are different. (InnoDB's is arguably better.)

mysql> SHOW VARIABLES LIKE '%ft%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    147
Current database: pics

These can be tuned:
+---------------------------------+----------------+
| Variable_name                   | Value          |
+---------------------------------+----------------+
| ft_boolean_syntax               | + -><()~*:""&| | MyISAM settings...
| ft_max_word_len                 | 84             |
| ft_min_word_len                 | 4              | -- Words shorter than 4 characters are ignored
| ft_query_expansion_limit        | 20             |
| ft_stopword_file                | (built-in)     |
| innodb_ft_aux_table             |                | InnoDB settings...
| innodb_ft_cache_size            | 8000000        |
| innodb_ft_enable_diag_print     | OFF            |
| innodb_ft_enable_stopword       | ON             |
| innodb_ft_max_token_size        | 84             |
| innodb_ft_min_token_size        | 3              | -- Words shorter than 3 characters are ignored
| innodb_ft_num_word_optimize     | 2000           |
| innodb_ft_server_stopword_table |                |
| innodb_ft_sort_pll_degree       | 2              |
| innodb_ft_user_stopword_table   |                |
+---------------------------------+----------------+

LIKE is a cross between exact match and wildcard match.
LIKE '%38mm x 21%' would match only '38mm x 21mm' from your list
LIKE '%38%21%' would match '38mm x 21mm' and '38 x 21', but also '1-800-238-4211'.

RLIKE (REGEXP) is more powerful, but complicated:
RLIKE '38( ?mm)? x 21|21( ?mm)? x 38' would catch 4 of the items on your list.

SQL is not the best way to do such searching.

Options: ReplyQuote


Subject
Written By
Posted
Re: broad match search results, splitting the search by spaces
November 12, 2014 11:03AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.