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 ͷ΄͏্͕ͬΆ͍

͋Γ͕ͱ͏ ͍͟͝·ͨ͠