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
Implementing and Evaluating a High-Level Language with WasmGC and the Wasm Component Model: Scala’s Case
tanishiking
0
140
Scala meets WebAssembly
tanishiking
0
650
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
86
Tooling for Scala3
tanishiking
0
410
API-Based Code Search
tanishiking
0
64
Things happening before start coding with Metals ~behind the curtain of daily coding~
tanishiking
0
620
Other Decks in Programming
See All in Programming
Web フロントエンドエンジニアに開かれる AI Agent プロダクト開発 - Vercel AI SDK を観察して AI Agent と仲良くなろう! #FEC余熱NIGHT
izumin5210
3
570
Domain-centric? Why Hexagonal, Onion, and Clean Architecture Are Answers to the Wrong Question
olivergierke
3
920
オープンソースソフトウェアへの解像度🔬
utam0k
16
3k
あなたとKaigi on Rails / Kaigi on Rails + You
shimoju
0
170
CSC305 Lecture 04
javiergs
PRO
0
270
Swift Concurrency - 状態監視の罠
objectiveaudio
2
550
Goで実践するドメイン駆動開発 AIと歩み始めた新規プロダクト開発の現在地
imkaoru
4
860
Pull-Requestの内容を1クリックで動作確認可能にするワークフロー
natmark
2
520
iOSエンジニア向けの英語学習アプリを作る!
yukawashouhei
0
200
アメ車でサンノゼを走ってきたよ!
s_shimotori
0
230
なぜあの開発者はDevRelに伴走し続けるのか / Why Does That Developer Keep Running Alongside DevRel?
nrslib
3
410
CSC305 Lecture 08
javiergs
PRO
0
250
Featured
See All Featured
Bootstrapping a Software Product
garrettdimon
PRO
307
110k
RailsConf & Balkan Ruby 2019: The Past, Present, and Future of Rails at GitHub
eileencodes
140
34k
Site-Speed That Sticks
csswizardry
13
910
Speed Design
sergeychernyshev
32
1.2k
Evolution of real-time – Irina Nazarova, EuRuKo, 2024
irinanazarova
9
980
Leading Effective Engineering Teams in the AI Era
addyosmani
7
490
Building Better People: How to give real-time feedback that sticks.
wjessup
369
20k
Exploring the Power of Turbo Streams & Action Cable | RailsConf2023
kevinliebholz
35
6.1k
Optimizing for Happiness
mojombo
379
70k
Keith and Marios Guide to Fast Websites
keithpitt
411
23k
It's Worth the Effort
3n
187
28k
Principles of Awesome APIs and How to Build Them.
keavy
127
17k
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 ͷ΄͏্͕ͬΆ͍
͋Γ͕ͱ͏ ͍͟͝·ͨ͠