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
120
InnoDBだってシュッと全文検索したい!
Rikito Taniguchi
January 11, 2016
Tweet
Share
More Decks by Rikito Taniguchi
See All by Rikito Taniguchi
Scala to WebAssembly: Exploring the How and Why
tanishiking
0
830
Scala to WebAssembly #scala_waiwai
tanishiking
0
1.1k
Scala Days Madrid 2023 参加レポート
tanishiking
0
51
Tooling for Scala3
tanishiking
0
360
API-Based Code Search
tanishiking
0
39
Things happening before start coding with Metals ~behind the curtain of daily coding~
tanishiking
0
550
How we replaced a 10-year-old Perl product using Scala
tanishiking
14
33k
Quick introduction to scalafix
tanishiking
1
160
Format things with scalafmt
tanishiking
1
1.9k
Other Decks in Programming
See All in Programming
Делим тесты между QA и разработчиком
mariyasaygina
0
250
フロントエンドの標準仕様をどう追っているか / How I follow the frontend standards specs
petamoriken
0
150
Applied NLP in the Age of Generative AI
inesmontani
PRO
3
1k
Boost Performance and Developer Productivity with Jakarta EE 11
ivargrimstad
0
900
Findy - エンジニア向け会社紹介 / Findy Letter for Engineers
findyinc
4
92k
unique パッケージから学ぶ interning と weak reference @ Asakusa.go#3
karamaru
2
860
なぜアジャイルがうまくいかないのか?
yum3
2
140
実践!難読化ガイド
mitchan
0
1.1k
GraphQLでいい感じの検索APIを作りたい
estie
0
130
Infrastructure as Code でセキュリティを楽にしよう!
konokenj
3
1.1k
Debugging: All you need to know (English Only)
jmatsu
0
140
宿泊予約サイトにおける検索と料金計算の両立
skaji
1
190
Featured
See All Featured
Practical Tips for Bootstrapping Information Extraction Pipelines
honnibal
PRO
6
530
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
45
4.8k
Fashionably flexible responsive web design (full day workshop)
malarkey
401
65k
The Cult of Friendly URLs
andyhume
76
6k
Code Reviewing Like a Champion
maltzj
517
39k
Let's Do A Bunch of Simple Stuff to Make Websites Faster
chriscoyier
502
140k
Teambox: Starting and Learning
jrom
131
8.7k
It's Worth the Effort
3n
182
27k
Done Done
chrislema
180
16k
4 Signs Your Business is Dying
shpigford
180
21k
Understanding Cognitive Biases in Performance Measurement
bluesmoon
26
1.3k
What's in a price? How to price your products and services
michaelherold
242
11k
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 ͷ΄͏্͕ͬΆ͍
͋Γ͕ͱ͏ ͍͟͝·ͨ͠