기본 InnoDB 전문 검색(Full Text) 파서는 공백이나 단어 분리자가 토큰인 라틴 기반 언어들에서는 이상적이지만 개별 단어에 대한 고정된 구분자가 없는 중국어, 일본어, 한국어(CJK)같은 언어들에서는 각 단어는 여러개의 문자들의 조합으로 이루어집니다. 그래서 이경우엔 단어 토큰들을 처리할 수 있는 다른 방법이 필요합니다.

우리는 CJK에서 사용할 수 있는 n-gram 파서를 제공하기 위한 새로운 플러그블 전문 파서(pluggable full-text parser)를 MySQL 5.7.6 에서 제공할 수 …

(この記事は InnoDB Full-Text: MeCab Parser を Yoshiaki Yamasaki が翻訳したものです)

このブログ記事で紹介した一般的なCJK(中国語、日本語、韓国語)のサポートに加えて、私達はMeCabパーサーも追加しました。MeCabは日本語の形態素解析エンジンで、私達は今 …

(この記事は InnoDB Full-Text : N-gram Parser を Yoshiaki Yamasaki が翻訳したものです)

デフォルトのInnoDB全文検索パーサー(構文解析プログラム)は、空白がトークン(語句)もしくは単語の区切りとなっているラテン語ベースの言語に対して理想的です。しかし、個々の単語の区切り文字が存在せず、それぞれの単語は複数の文字で構成できる中国語・日本語・韓国語(CJK)のような言語には向いていません。そこで、私たちは異なった方法で単語/トークンを識別する方法を必要とします。
私は今 …

In addition to our general CJK support, as detailed in this blog post, we’ve also added a MeCab parser. MeCab is a Japanese morphological analyzer, and we now have a full-text plugin parser based on it!

How Would I Use It?

1. Set the mecab_rc_file option — mecab_rc_file is a read-only system variable pertaining to the MeCab parser. The mecabrc file that it points to is a configuration file required by MeCab, …

The default InnoDB full-text parser is ideal for latin based languages where whitespace is the token or word separator, but for languages like Chinese, Japanese, and Korean (CJK)—where there is no fixed separators for individual words, and each word can be compromised of multiple characters—we need a different way to handle the word tokens. I’m now very happy to say that in MySQL 5.7.6 we’ve made use of the new pluggable full-text parser support in order to provide you with an n-gram parser that can be used with CJK!

…

Full text is a critical point when it comes to mysql. It used to have that feature in MyISAM but that’s not really maintained anymore nor it is advised to use unless you have a very specific use case in which it might make sense. There are 3rd party solution which takes the problem away (Lucene, Sphinx, Solr, ElasticSearch) but all bring extra complexity to your setup which has its own cost. So do you need to give up on fulltext search if you’re using MySQL + InnoDB? No! Definitely not.

Alternatives for full-text search in MySQL

I have an live database with 9000+ products with category, brand and short description which is perfect to test my searches on. I’m going to run the queries many times and use profile information to collect more granular and accurate timing information.

LIKE

Well… It’s not a real alternative but just for a sec see how well it behaves.

SELECT SQL_NO_CACHE b.name, …

Summary
Since MySQL 5.6 went GA—where among many other new features, we introduced full-text indexes for the InnoDB storage engine—questions have occasionally come up about InnoDB full-text search relevancy rankings when doing BOOLEAN MODE searches. Typically these questions revolved around a core issue: why do the results differ from that of MyISAM? In short, the InnoDB document search and relevancy rankings were modeled after the open source Sphinx full-text search engine, and not after the MyISAM storage engine (which sometimes simply produces incorrect results). The algorithms used for the InnoDB implementation are based on the well known …

Summary
This post will offer a very simple demonstration of how you can use MySQL 5.7 for Spatial features within your applications. In order to demonstrate this, I’ll walk through how we can determine the 10 closest Thai restaurants to a particular location. For this example, we’ll be using the apartment that I lived in when I first started working at MySQL, back in 2003.

For more details on all of the new GIS related work that we’ve done in MySQL 5.7, please read through these blog posts from the developers:

• Why Boost.Geometry in MySQL?
• Making Use of …

I currently have on a MySQL 5.6 database using innodb_file_per_table the following individual tablespace file.

schema/FTS_00000000000001bb_BEING_DELETED.ibd

The schema is all InnoDB tables, and there ARE NO Full Text Indexes. I cannot comment on if a developer has tried to create one previously.
I am none the wiser in explaining the ongoing use of these files, or if it can be/should be deleted.

On closer inspection there are infact a number of FTS files.

$ ls -al FTS*
-rw-r----- 1 mysql mysql 98304 Jan 29 16:21 FTS_00000000000001bb_BEING_DELETED_CACHE.ibd
-rw-r----- 1 mysql mysql 98304 Jan 29 16:20 FTS_00000000000001bb_BEING_DELETED.ibd
-rw-r----- 1 mysql mysql 98304 Jan 29 16:26 FTS_00000000000001bb_CONFIG.ibd
-rw-r----- 1 mysql mysql 98304 Jan 29 16:21 FTS_00000000000001bb_DELETED_CACHE.ibd
-rw-r----- 1 mysql mysql 98304 Jan 29 16:00 FTS_00000000000001bb_DELETED.ibd
-rw-r----- 1 mysql mysql 98304 Jan 29 16:20 …

InnoDB Fulltext Search now supports plugin parser in MySQL 5.7.3 release. It is a compatible feature as for MyISAM Fulltext Search. So the syntax and usage remain to be largely the same.

A parser plugin can operate in either of two roles:

a) The plugin can replace the built-in parser. In this role, the plugin reads the input to be parsed, splits it up into words, and passes the words to the server (either for indexing or for word accumulation).

b) The plugin can act in conjunction with the built-in parser by serving as a front end for it. In this role, the plugin extracts text from the input and passes the text to the parser, which splits up the text into words using its normal parsing rules.

If you want to write your own full text plugin, please refer to http://dev.mysql.com/doc/refman/5.7/en/writing-full-text-plugins.html.

If you have a existing plugin parser for MyISAM, there would be some minor …