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
530
Why Wasm+WASI for Scala
tanishiking
0
110
Scala to WebAssembly: Exploring the How and Why
tanishiking
0
1.7k
Scala to WebAssembly #scala_waiwai
tanishiking
0
1.6k
Scala Days Madrid 2023 参加レポート
tanishiking
0
82
Tooling for Scala3
tanishiking
0
400
API-Based Code Search
tanishiking
0
53
Things happening before start coding with Metals ~behind the curtain of daily coding~
tanishiking
0
600
How we replaced a 10-year-old Perl product using Scala
tanishiking
14
33k
Other Decks in Programming
See All in Programming
LINEヤフー データグループ紹介
lycorp_recruit_jp
0
760
Benchmark
sysong
0
220
イベントストーミングから始めるドメイン駆動設計
jgeem
4
870
コード書くの好きな人向けAIコーディング活用tips #orestudy
77web
3
320
從零到一:搭建你的第一個 Observability 平台
blueswen
1
960
ASP.NETアプリケーションのモダナイズ インフラ編
tomokusaba
1
390
第9回 情シス転職ミートアップ 株式会社IVRy(アイブリー)の紹介
ivry_presentationmaterials
1
190
Enterprise Web App. Development (2): Version Control Tool Training Ver. 5.1
knakagawa
1
120
統一感のある Go コードを生成 AI の力で手にいれる
otakakot
0
3k
Haskell でアルゴリズムを抽象化する / 関数型言語で競技プログラミング
naoya
17
4.8k
業務自動化をJavaとSeleniumとAWS Lambdaで実現した方法
greenflagproject
1
120
ktr0731/go-mcpでMCPサーバー作ってみた
takak2166
0
170
Featured
See All Featured
Designing for Performance
lara
609
69k
Bash Introduction
62gerente
614
210k
Keith and Marios Guide to Fast Websites
keithpitt
411
22k
StorybookのUI Testing Handbookを読んだ
zakiyama
30
5.8k
A Tale of Four Properties
chriscoyier
159
23k
Distributed Sagas: A Protocol for Coordinating Microservices
caitiem20
331
22k
ReactJS: Keep Simple. Everything can be a component!
pedronauck
667
120k
Let's Do A Bunch of Simple Stuff to Make Websites Faster
chriscoyier
507
140k
What’s in a name? Adding method to the madness
productmarketing
PRO
22
3.5k
Exploring the Power of Turbo Streams & Action Cable | RailsConf2023
kevinliebholz
32
5.9k
Adopting Sorbet at Scale
ufuk
77
9.4k
RailsConf 2023
tenderlove
30
1.1k
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 ͷ΄͏্͕ͬΆ͍
͋Γ͕ͱ͏ ͍͟͝·ͨ͠