CODEWITHSUNDEEP
mysql
pro game
pro game

Reputation: 21

mysql fulltext search cannot search dollar sign?

Going to refine the problem a bit

The description column contains USD$15, USD15 among a bunch of text.

using fulltext search IN BOOLEAN MODE but empty result returned

select * from books WHERE MATCH (description) AGAINST ('USD$15' IN BOOLEAN MODE); Empty set (0.00 sec)

but

select * from books WHERE MATCH (description) AGAINST ('USD15' IN BOOLEAN MODE);

is successfully......

Upvotes: 2

Views: 869

Answers (3)

Zagor23
Zagor23

Reputation: 1963

You are trying to match against 3 character string, while the default is 4 (I guess that is in your case also).

It says on this page of MySQL docs:

Any word that is too short is ignored. The default minimum length of words that are found by full-text searches is four characters.

Check this for fine tuning full-text searches:

For example, if you want three-character words to be searchable, 
you can set the ft_min_word_len variable by putting the following 
lines in an option file:

[mysqld]
ft_min_word_len=3

UPDATE:

From MySQL documentation:

A phrase that is enclosed within double quote (“"”) 
characters matches only rows that contain the 
phrase literally, as it was typed. 
The full-text engine splits the phrase into words 
and performs a search in the FULLTEXT index for the words. 
Nonword characters need not be matched exactly: 
Phrase searching requires only that matches contain exactly 
the same words as the phrase and in the same order. 
For example, "test phrase" matches "test, phrase".

Which basically means that you could try fulltext search enclosing search phrase in double quotes, and you could get results you need as long as dollar sign is the only special character between USD and price in your column values (also no spaces between them). However, FULLTEXT search is made for word searching, and special characters are ignored, so relying on this is very risky.

If changes in database are out of the question, maybe you can consider SPHINX for fulltext search.

Upvotes: 0

Matthew
Matthew

Reputation: 9949

This may be your problem, and not the $

Some words are ignored in full-text searches: Any word that is too short is ignored. The default minimum length of words that are found by full-text searches is four characters.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

You can change this setting here - but be sure to consider the consequences of all the other 3-char words which will be indexed: my.cnf on this setting: ft_min_word_len = 3

Also - @webdevbyjoss nails the actual concern - this is better handled outside of full text searching if just looking to match prices.

UPDATE You are correct, you also need to add the $ as a valid / included character or it will be ignored. From the documentation:

If you want to change the set of characters that are considered word characters, you can do so in several ways, as described in the following list. After making the modification, you must rebuild the indexes for each table that contains any FULLTEXT indexes. Suppose that you want to treat the hyphen character ('-') as a word character. Use one of these methods:

  1. Modify the MySQL source: In myisam/ftdefs.h, see the true_word_char() and misc_word_char() macros. Add '-' to one of those macros and recompile MySQL.
  2. Modify a character set file: This requires no recompilation. The true_word_char() macro uses a “character type” table to distinguish letters and numbers from other characters. . You can edit the contents of the array in one of the character set XML files to specify that '-' is a “letter.” Then use the given character set for your FULLTEXT indexes. For information about the array format, see Section 10.3.1, “Character Definition Arrays”.
  3. Add a new collation for the character set used by the indexed columns, and alter the columns to use that collation. For general information about adding collations, see Section 10.4, “Adding a Collation to a Character Set”. For an example specific to full-text indexing, see Section 12.9.7, “Adding a Collation for Full-Text Indexing”.

Upvotes: 1

webdevbyjoss
webdevbyjoss

Reputation: 524

Don't use full text search for prices, because it is designed to produce the relevance between query and appropriate text. So it allows to search for particular keywords in large amounts of texts. Index contains tokenized versions of words and can't be used for character by character match.

Use something like DECIMAL(15,4) instead and move currency setting into separate field (ENUM/TINYINT)

In worst case (you can't influence table structure and data inserted) - you can use regular CHAR/VARCHAR with BTREE index and search via WHERE price LIKE ... approach.

Upvotes: 3

Related Questions