【MySQL】SQLAlchemy内でMySQLの全文検索を試めしてみた

MySQLの設定は以下を参考
thoames.hatenadiary.jp

検索対象のインデックスがつけられる単語の最小長と最大長の設定

InnoDBなら「innodb_ft_min_token_size」、MyISAMなら「ft_min_word_len」で最小長を設定
InnoDBなら「innodb_ft_max_token_size」、MyISAMなら「ft_max_word_len」で最大長を設定

自分が使用するのはInnoDBなので、InnoDB用の設定を行う。

innodb_ft_min_token_sizeのデフォルトは「3」、innodb_ft_max_token_sizeは「84」
MySQL :: MySQL 5.7 Reference Manual :: 14.15 InnoDB Startup Options and System Variables
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.9.6 MySQL の全文検索の微調整

/usr/local/etc/my.cnf

[mysqld]
innodb_ft_min_token_size=2
ft_min_word_len=2

設定の確認

mysql> SHOW VARIABLES like 'innodb_ft_min_token_size';

変更を有効にするためには、mysqldを再起動し、INDEXを再作成する必要がある

検索の仕方

ヒット率50%に準じた検索（全体の50%を超えたら出力されない）

SELECT * FROM products
WHERE 
 MATCH(search)
     against(ngram('protein'));

ヒット率を無視した検索

SELECT * FROM products
WHERE 
 MATCH(search)
     against(ngram('protein') in boolean mode );

検索語での結果に加えて、類似・関連語の結果も返す検索

SELECT * FROM products
WHERE 
 MATCH(search)
     against(ngram('protein') with query expansion );

以下の記事を参考
MySQLで全文検索2 - SQLer 生島勘富のブログ
 MySQLで全文検索 - FULLTEXTインデックスの基礎知識｜blog｜たたみラボ

Flask内で使用する

customers = session.query(Customer).filter(Customer.name.match("anomaly")).all()

現在は単一テーブルの単一カラムを対象にした検索で「BOOLEAN mode」のみしかデフォルトだと使用できない。
もし複数テーブルの複数カラム且つ「with query expansion」などを対象にした検索がしたい場合は
「ClauseElement」を使用し、自らクエリを組み立てる必要があるとのこと。
stackoverflow.com

将来的にはSQLAlchemyでサポートされるっぽい。

以下の記事を参考
Python SQLAlchemy + MySQL で複数フィールドに対する全文検索：MATCH AGAINST 文を実行する - Qiita
MySQL full-text searches and SQLAlchemy; the present and a proposed future | Anomaly
MySQL — SQLAlchemy 1.3 Documentation

[他参考]
LIKE検索より50倍速い！？MySQLでラクラク高速な日本語全文検索 - bitA Tech Blog
innodb - MySQL FullText search on string shorter than 3 chars returns no rows - Database Administrators Stack Exchange

とーますメモ

Ruby on Rails / Goなどの学習メモ