Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
InnoDBだってシュッと全文検索したい!
Search
Rikito Taniguchi
January 11, 2016
Programming
0
130
InnoDBだってシュッと全文検索したい!
Rikito Taniguchi
January 11, 2016
Tweet
Share
More Decks by Rikito Taniguchi
See All by Rikito Taniguchi
Scala meets WebAssembly
tanishiking
0
640
Why Wasm+WASI for Scala
tanishiking
0
120
Scala to WebAssembly: Exploring the How and Why
tanishiking
1
1.7k
Scala to WebAssembly #scala_waiwai
tanishiking
0
1.7k
Scala Days Madrid 2023 参加レポート
tanishiking
0
85
Tooling for Scala3
tanishiking
0
410
API-Based Code Search
tanishiking
0
62
Things happening before start coding with Metals ~behind the curtain of daily coding~
tanishiking
0
610
How we replaced a 10-year-old Perl product using Scala
tanishiking
14
34k
Other Decks in Programming
See All in Programming
Back to the Future: Let me tell you about the ACP protocol
terhechte
0
120
育てるアーキテクチャ:戦い抜くPythonマイクロサービスの設計と進化戦略
fujidomoe
1
120
なぜGoのジェネリクスはこの形なのか? Featherweight Goが明かす設計の核心
ryotaros
7
880
PostgreSQLで手軽にDuckDBを使う!DuckDB&pg_duckdb入門/osk2025-duckdb
takahashiikki
1
220
AIエージェント時代における TypeScriptスキーマ駆動開発の新たな役割
bicstone
4
1.1k
開発者への寄付をアプリ内課金として実装する時の気の使いどころ
ski
0
280
Breaking Up with Big ViewModels — Without Breaking Your Architecture (droidcon Berlin 2025)
steliosf
PRO
1
220
Local Peer-to-Peer APIはどのように使われていくのか?
hal_spidernight
2
390
複雑化したリポジトリをなんとかした話 pipenvからuvによるモノレポ構成への移行
satoshi256kbyte
0
400
検索機能リプレイスを4ヶ月→2ヶ月に! AI Agentで実現した2倍速リプレイス
fuuki12
4
800
Astroの使用感とディレクトリ設計についての考察
saku0109
0
160
高度なUI/UXこそHotwireで作ろう Kaigi on Rails 2025
naofumi
4
1.5k
Featured
See All Featured
Speed Design
sergeychernyshev
32
1.1k
Agile that works and the tools we love
rasmusluckow
330
21k
Understanding Cognitive Biases in Performance Measurement
bluesmoon
29
2.6k
What's in a price? How to price your products and services
michaelherold
246
12k
I Don’t Have Time: Getting Over the Fear to Launch Your Podcast
jcasabona
33
2.4k
VelocityConf: Rendering Performance Case Studies
addyosmani
332
24k
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
rmw
35
3.2k
Docker and Python
trallard
46
3.6k
Automating Front-end Workflow
addyosmani
1371
200k
XXLCSS - How to scale CSS and keep your sanity
sugarenia
248
1.3M
Site-Speed That Sticks
csswizardry
10
850
Imperfection Machines: The Place of Print at Facebook
scottboms
269
13k
Transcript
InnoDBͩͬͯγϡοͱ શจݕࡧ͍ͨ͠ʂ 2016/01/11 (݄) ୈೋճ CAMPHOR- × KMC ߹ಉ LTେձ
͜Μʹͪʂ ɾ@tanishiking ɾژେֶֶ෦ใֶՊ B3 ɾڈͷ4݄͙Β͍͔Β CAMPHOR- ɾDBͱ͔ʹڵຯ͋Δ(Ͱ͖Δͱݴͬͯͳ͍)
EXPLAIN ͯ͠·͔͢ʁ
SELECT * FROM table WHERE text LIKE ‘%foo%’;
SELECT * FROM table WHERE text LIKE ‘%foo%’; ී௨ͷΠϯσοΫεͩͱLIKEݕࡧલํҰக Ͱͳ͍ͱΠϯσοΫε͕༻͞Εͳ͍
͔ͳ͍͠
ͳΒ શจݕࡧͩʂ
શจݕࡧΤϯδϯͱ͔͍Ζ͍Ζ ͋Δ͚Ͳڥͱͱͷ͑Δͷେม ࠓ͋ΔڥͰεοͱ શจݕࡧ͍ͨ͠
MySQL 5.6.4 ͔Β ϑϧςΩετΠ ϯσοΫε͕ InnoDB Ͱ͑ Δʂ
MySQL 5.6.4 ͔Β ϑϧςΩετΠ ϯσοΫε͕ InnoDB Ͱ͑ Δʂ ※ͨͩ͠ຊޠͷΑ͏ͳݸʑͷ୯ޠʹ۠Γจࣈ͕ͳ͍ݴޠαϙʔτ͠ͳ͍ͷͱ͢Δ
MySQL 5.6.4 ͔Β ϑϧςΩετΠ ϯσοΫε͕ InnoDB Ͱ͑ Δʂ ※ͨͩ͠ຊޠͷΑ͏ͳݸʑͷ୯ޠʹ۠Γจࣈ͕ͳ͍ݴޠαϙʔτ͠ͳ͍ͷͱ͢Δ Ͳ͏͍͏͜ͱʁ
ຊޠΛ۠ΔͨΊʹʁ mecab ͢ͷ͏ͪ ͢ ໊ࢺ,Ұൠ,*,*,*,*,͢,εϞϞ,εϞϞ ॿࢺ,ॿࢺ,*,*,*,*,,Ϟ,Ϟ ໊ࢺ,Ұൠ,*,*,*,*,,ϞϞ,ϞϞ
ॿࢺ,ॿࢺ,*,*,*,*,,Ϟ,Ϟ ໊ࢺ,Ұൠ,*,*,*,*,,ϞϞ,ϞϞ ͷ ॿࢺ,࿈ମԽ,*,*,*,*,ͷ,ϊ,ϊ ͏ͪ ໊ࢺ,ඇཱࣗ,෭ࢺՄೳ,*,*,*,͏ͪ,ν,ν ͔ͪॻ͖
ຊޠΛ۠ΔͨΊʹʁ ngram ςΩετΛnจࣈ͝ͱʹ۠Δ bigram (n = 2) ͳΒ ͜Μʹͪ ↓
͜Μ, Μʹ, ʹͪ. ͪ
͘…ۭനͰ۠ΒΕͨ จষ ͍͚ΔΜͰ͠ΐ…???
CREATE TABLE `test` ( `id` AUTO_INCREMENT NOT NULL PRIMARY KEY,
`text` varchar(255) NOT NULL, FULLTEXT KEY `ngram_idx` (`text`) ) Engine=InnoDB DEFAULT CHARSET=utf-8; INSERT INTO `test` (`text`) VALUES ( “͢ɹɹɹɹɹͷɹ͏ͪ” ); INSERT INTO `test` (`text`) VALUES ( “͜ΜɹΜʹɹʹͪɹͪ” );
҆৺ͯ͠ ͍ͩ͘͞ :)
MySQL 5.7.6 ͔Βຊޠɾதࠃ ޠɾؖࠃޠͰར༻Մೳͳ ngram ύʔαʔ͕ InnoDB Ͱαϙʔτʂ μϯϩʔυ͢Εmecabύʔαʔ͑ΔΑʂ
CREATE TABLE `test` ( `id` AUTO_INCREMENT NOT NULL PRIMARY KEY,
`text` varchar(255) NOT NULL, FULLTEXT KEY `ngram_idx` (`text`) WITH PARSER ngram ) Engine=InnoDB DEFAULT CHARSET=utf-8; INSERT INTO `test` (`text`) VALUES ( “͢ͷ͏ͪ” ); ngram ͷ n ͷ ngram_token_size ͰઃఆՄೳ(σϑΥϧτ2)
SELECT * FROM table WHERE text LIKE ‘%foo%’;
SELECT * FROM table WHERE (MATCH (text) AGAINST (‘“foo”’ IN
BOOLEAN MODE));
ɾΠϯσοΫεαΠζ͕Ͱ͔͍ ɾΠϯσοΫε࡞ʹΊͬͪΌ͔͔࣌ؒΔ
MySQL + groonga = mroonga ଞͷબࢶ ߋ৽ɾૠೖੑೳ mroonga ͷ΄͏্͕ ݕࡧੑೳ
InnoDB fulltext index ͷ΄͏্͕ͬΆ͍
͋Γ͕ͱ͏ ͍͟͝·ͨ͠