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
500
Why Wasm+WASI for Scala
tanishiking
0
110
Scala to WebAssembly: Exploring the How and Why
tanishiking
0
1.6k
Scala to WebAssembly #scala_waiwai
tanishiking
0
1.5k
Scala Days Madrid 2023 参加レポート
tanishiking
0
78
Tooling for Scala3
tanishiking
0
400
API-Based Code Search
tanishiking
0
52
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
REST API設計の実践 – ベストプラクティスとその落とし穴
kentaroutakeda
2
320
iOSアプリ開発もLLMで自動運転する
hiragram
6
2.1k
マテリアルって何者?RealityKitで扱うマテリアル入門
nao_randd
0
140
Interface vs Types ~型推論が過多推論~
hirokiomote
1
230
Parallel::Pipesの紹介
skaji
2
870
インターフェース設計のコツとツボ
togishima
2
490
〜可視化からアクセス制御まで〜 BigQuery×Looker Studioで コスト管理とデータソース認証制御する方法
cuebic9bic
1
270
TypeScript を活かしてデザインシステム MCP を作る / #tskaigi_after_night
izumin5210
4
480
Building an Application with TDD, DDD and Hexagonal Architecture - Isn't it a bit too much?
mufrid
0
370
Spring gRPC で始める gRPC 入門 / Introduction to gRPC with Spring gRPC
mackey0225
0
120
Proxmoxをまとめて管理できるコンソール作ってみました
karugamo
1
410
從零到一:搭建你的第一個 Observability 平台
blueswen
0
220
Featured
See All Featured
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
PRO
180
53k
Gamification - CAS2011
davidbonilla
81
5.3k
Automating Front-end Workflow
addyosmani
1370
200k
Exploring the Power of Turbo Streams & Action Cable | RailsConf2023
kevinliebholz
32
5.8k
Sharpening the Axe: The Primacy of Toolmaking
bcantrill
42
2.3k
XXLCSS - How to scale CSS and keep your sanity
sugarenia
248
1.3M
The Cost Of JavaScript in 2023
addyosmani
49
8.1k
Distributed Sagas: A Protocol for Coordinating Microservices
caitiem20
331
21k
Rebuilding a faster, lazier Slack
samanthasiow
81
9k
Embracing the Ebb and Flow
colly
85
4.7k
The Language of Interfaces
destraynor
158
25k
Adopting Sorbet at Scale
ufuk
76
9.4k
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 ͷ΄͏্͕ͬΆ͍
͋Γ͕ͱ͏ ͍͟͝·ͨ͠