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
Sponsored
·
SiteGround - Reliable hosting with speed, security, and support you can count on.
→
Rikito Taniguchi
January 11, 2016
Programming
140
0
Share
Embed
Copy iframe code
Copy JS code
Copy link
Start on current slide
InnoDBだってシュッと全文検索したい!
Rikito Taniguchi
January 11, 2016
More Decks by Rikito Taniguchi
See All by Rikito Taniguchi
GC言語のWasm化とComponent Modelサポートの実践と課題 - Scalaの場合
tanishiking
0
180
Capture Checking / Separation Checking 入門
tanishiking
0
590
Implementing and Evaluating a High-Level Language with WasmGC and the Wasm Component Model: Scala’s Case
tanishiking
0
740
Scala meets WebAssembly
tanishiking
0
1k
Why Wasm+WASI for Scala
tanishiking
0
140
Scala to WebAssembly: Exploring the How and Why
tanishiking
1
1.9k
Scala to WebAssembly #scala_waiwai
tanishiking
0
1.9k
Scala Days Madrid 2023 参加レポート
tanishiking
0
110
Tooling for Scala3
tanishiking
0
450
Other Decks in Programming
See All in Programming
技術記事、 専門家としてのプログラマ、 言語化
mizchi
13
6.1k
生成AI時代にこそ効くGo | Why Go Works in the Age of Generative AI
mom0tomo
8
3.3k
JavaDoc 再入門
nagise
1
370
AI時代のUIはどこへ行く?その2!
yusukebe
21
7.3k
技術記事、AIに書かせるか、自分で書くか? 〜それでも私が自分の手で書く理由〜 / #QiitaConference
jnchito
2
1.4k
TSKaigi Night Talks 2026_TypeScriptでサプライチェーンの整合性を型に閉じ込める
geekplus_tech
0
390
軽量Java基盤の設計 DIコンテナに頼らない、長期保守と1秒起動の実現 JJUG CCC 2026 Spring
macha64
0
540
AI 時代のソフトウェア設計の学び方
masuda220
PRO
29
13k
Java × distroless で 軽量なコンテナイメージを / Java on Distroless
contour_gara
0
550
気圧・高度・GPSを記録&可視化するアプリ「Koudo」を作った話
hjmkth
1
270
Inside Stream API
skrb
1
730
Skillsは効率化、Agentsは"自分の拡張"——Builder時代のエージェント編成(CC Night 2026)
wemra
1
140
Featured
See All Featured
Stop Working from a Prison Cell
hatefulcrawdad
274
21k
"I'm Feeling Lucky" - Building Great Search Experiences for Today's Users (#IAC19)
danielanewman
230
23k
The Success of Rails: Ensuring Growth for the Next 100 Years
eileencodes
47
8.2k
Winning Ecommerce Organic Search in an AI Era - #searchnstuff2025
aleyda
1
2k
svc-hook: hooking system calls on ARM64 by binary rewriting
retrage
2
300
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
128
56k
Mind Mapping
helmedeiros
PRO
1
250
So, you think you're a good person
axbom
PRO
2
2.1k
Optimizing for Happiness
mojombo
378
71k
The B2B funnel & how to create a winning content strategy
katarinadahlin
PRO
1
390
Large-scale JavaScript Application Architecture
addyosmani
515
110k
The Curious Case for Waylosing
cassininazir
1
390
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 ͷ΄͏্͕ͬΆ͍
͋Γ͕ͱ͏ ͍͟͝·ͨ͠