The problem that uses Sphinx to search for subdomains in Mysql requires attention _mysql

Search Sub Domain

For example, search jb51.net will www.jb51.net, jb51.net, host.jb51.net, etc. are searched out.

If the use of MySQL with like words efficiency is very low, millions of or even tens of millions of data that is no way to use, so take Sphinx to do.

In the use of the process to find a lot of problems, summed up here, but also let not know friends to notice these characters.

Analysis:
Sphinx is a full-text index that searches for included records.

First of all, if we do not make any settings, search jb51.net will appear aajb51.net, jb51.a.cn, jb51.net.com (of course, this domain name suffix does not exist, but there will be a corresponding rule of the domain name, here only for example) and so on.

Why is this happening?

When we search using the./search-c configuration file-I index name ' jb51.net ', we find that the following words section is divided into two parts ' jb51 ' and ' CN ', by default. As a delimiter, if we don't want it to be a separator, we need to add. to Charset_ Table inside, we need to say that we search the domain name only need letters, numbers, "-" and other characters, others do not need. The settings are as follows:
charset_table = 0..9,a. Z->a.. Z, A.. z,u+002e,u+002d,u+0040,u+0060 #其中U +002e represents ".", u+002d represents "-", u+0040 represents "@", and u+0060 represents "'", here is ASCII value.
This will solve the search out jb51.a.cn such domain names.

So what about the jb51.net.com? We can add a unique suffix to the field in the index such as "xxxxx", concat (Search, ' xxxxx ') so that it will not come out.

Now left aajb51.net such domain names, we use the keyword "'". Jb51.net "'" (note the single quotation mark inside the double quotes) so to search, the main domain name alone, but the use of the process found that this domain name is not related to the domain name, such as aa.bb.cn, after which the study was "." Question, and later put "." Replaced by "@", to search for a number of 12306 and so many domain names can not read the problem, and then the study estimates that these special characters in the Sphinx have a special meaning, and found the "'" this character, replaced by this later everything is normal.

Note: the "." Replace with the corresponding characters such as "'" to add this character in the charset_table, otherwise it is ignored.

So in the search process we need to be aware of these special characters.