InnoDBだってシュッと全文検索したい！

Transcript

1. InnoDBͩͬͯγϡοͱ શจݕࡧ͍ͨ͠ʂ 2016/01/11 (݄)
 ୈೋճ CAMPHOR- × KMC ߹ಉ LTେձ
2. ͜Μʹͪ͸ʂ ɾ@tanishiking ɾژ౎େֶ޻ֶ෦৘ใֶՊ B3 ɾڈ೥ͷ4݄͙Β͍͔Β CAMPHOR- ɾDBͱ͔ʹڵຯ͋Δ(Ͱ͖Δͱ͸ݴͬͯͳ͍)
3. EXPLAIN ͯ͠·͔͢ʁ
4. SELECT * FROM table WHERE text LIKE ‘%foo%’;
5. SELECT * FROM table WHERE text LIKE ‘%foo%’; ී௨ͷΠϯσοΫεͩͱLIKEݕࡧ͸લํҰக Ͱͳ͍ͱΠϯσοΫε͕࢖༻͞Εͳ͍
6. ͔ͳ͍͠
7. ͳΒ͹ શจݕࡧͩʂ
8. શจݕࡧΤϯδϯͱ͔͍Ζ͍Ζ ͋Δ͚Ͳ؀ڥͱͱͷ͑Δͷେม ࠓ͋Δ؀ڥͰεοͱ શจݕࡧ͍ͨ͠
9. MySQL 5.6.4 ͔Β ϑϧςΩετΠ ϯσοΫε͕ InnoDB Ͱ΋࢖͑ Δʂ
10. MySQL 5.6.4 ͔Β ϑϧςΩετΠ ϯσοΫε͕ InnoDB Ͱ΋࢖͑ Δʂ ※ͨͩ͠೔ຊޠͷΑ͏ͳݸʑͷ୯ޠʹ۠੾Γจࣈ͕ͳ͍ݴޠ͸αϙʔτ͠ͳ͍΋ͷͱ͢Δ
11. MySQL 5.6.4 ͔Β ϑϧςΩετΠ ϯσοΫε͕ InnoDB Ͱ΋࢖͑ Δʂ ※ͨͩ͠೔ຊޠͷΑ͏ͳݸʑͷ୯ޠʹ۠੾Γจࣈ͕ͳ͍ݴޠ͸αϙʔτ͠ͳ͍΋ͷͱ͢Δ Ͳ͏͍͏͜ͱʁ
12. ೔ຊޠΛ۠੾ΔͨΊʹ͸ʁ mecab ͢΋΋΋΋΋΋΋΋ͷ͏ͪ ͢΋΋ ໊ࢺ,Ұൠ,*,*,*,*,͢΋΋,εϞϞ,εϞϞ ΋ ॿࢺ,܎ॿࢺ,*,*,*,*,΋,Ϟ,Ϟ ΋΋ ໊ࢺ,Ұൠ,*,*,*,*,΋΋,ϞϞ,ϞϞ ΋ ॿࢺ,܎ॿࢺ,*,*,*,*,΋,Ϟ,Ϟ ΋΋ ໊ࢺ,Ұൠ,*,*,*,*,΋΋,ϞϞ,ϞϞ ͷ ॿࢺ,࿈ମԽ,*,*,*,*,ͷ,ϊ,ϊ ͏ͪ ໊ࢺ,ඇཱࣗ,෭ࢺՄೳ,*,*,*,͏ͪ,΢ν,΢ν ෼͔ͪॻ͖
13. ೔ຊޠΛ۠੾ΔͨΊʹ͸ʁ ngram ςΩετΛnจࣈ͝ͱʹ۠੾Δ bigram (n = 2) ͳΒ ͜Μʹͪ͸ ↓ ͜Μ, Μʹ, ʹͪ. ͪ͸
14. ͘…ۭനͰ۠੾ΒΕͨ จষ͸ ͍͚ΔΜͰ͠ΐ…???
15. 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 ( “͜ΜɹΜʹɹʹͪɹͪ͸” );
16. ҆৺ͯ͠ ͍ͩ͘͞ :)
17. MySQL 5.7.6 ͔Β೔ຊޠɾதࠃ ޠɾؖࠃޠͰར༻Մೳͳ ngram ύʔαʔ͕ InnoDB Ͱαϙʔτʂ μ΢ϯϩʔυ͢Ε͹mecabύʔαʔ΋࢖͑ΔΑʂ
18. 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)
19. SELECT * FROM table WHERE text LIKE ‘%foo%’;
20. SELECT * FROM table WHERE (MATCH (text) AGAINST (‘“foo”’ IN BOOLEAN MODE));
21. ɾΠϯσοΫεαΠζ͕Ͱ͔͍ ɾΠϯσοΫε࡞੒ʹΊͬͪΌ͔͔࣌ؒΔ
22. MySQL + groonga = mroonga ଞͷબ୒ࢶ ߋ৽ɾૠೖੑೳ͸ mroonga ͷ΄͏্͕ ݕࡧੑೳ͸ InnoDB fulltext index ͷ΄͏্͕ͬΆ͍
23. ͋Γ͕ͱ͏ ͍͟͝·ͨ͠