![ձZȤνy
The worst is not, So long as we can say,
ӢZΈ
]
]
]
]
]
]
]
]
]
gZФ꤬֤䤹狼䤹
MySQL5.6ǵLjInnoDB Full Text Search⥹ک`ФͶ뤷ǩ`ǰȤƤ
ձZnZйZ
OοͤϤ褯ʳͤ
ΑBؽʹä狼N-gramˤַֽФҪ
5](https://image.slidesharecdn.com/mroonga-140420234325-phpapp01/85/Mroonga_-20140418-5-320.jpg)
![MySQLʹNȫė
13
Tritonn mroonga MySQL-ftppc InnoDB FTS
InnoDB
MyISAM
MeCab
N-gram
MySQL5.0
MySQL5.1
MySQL5.5
MySQL5.6
6 [gh] MySQLȫėTritonn顸mroongaؤХɣ1
http://gihyo.jp/dev/clip/01/groonga/0006?page=1](https://image.slidesharecdn.com/mroonga-140420234325-phpapp01/85/Mroonga_-20140418-13-320.jpg)
More Related Content
Viewers also liked (16)
More from ˥㏊ `ӥ (20)
ȫėѰǴDzԲ奨˥ǿ20140418
- 4. ȫėʽ (index) ǰǩ`(index)ɤ٤ʗgF 4 | եΈ | եθ | Flȡ
- 5. ձZȤνy The worst is not, So long as we can say, ӢZΈ ] ] ] ] ] ] ] ] ] gZФ꤬֤䤹狼䤹 MySQL5.6ǵLjInnoDB Full Text Search⥹ک`ФͶ뤷ǩ`ǰȤƤ ձZnZйZ OοͤϤ褯ʳͤ ΑBؽʹä狼N-gramˤַֽФҪ 5
- 6. ձZη1?ΑBؽ ΑBؽȴǕʹä狼Ф OοͤϤ褯ʳͤ mФʴǕʹäƤmФ˥ѩ`롣 IPAǕӛµȤ`ѥʹäƤΤLjԤ¤ϵ # mecab OοͤϤ褯ʳͤ O ~,һ,*,*,*,*,O,ȥʥ,ȥʥ ~,B廯,*,*,*,*,,, ~,һ,*,*,*,*,,㥯,㥯 ~,S~,*,*,*,*,,, 褯 ~,һ,*,*,*,*,褯,襯,襯 ~,һ,*,*,*,*,,, ʳ ~,,*,*,?д,,ʳ,, ~,һ,*,*,*,*,,㥯,㥯 ~,*,*,*,?,,,, EOS 6
- 7. mecab+IPADicǥƥ`Zȡ # mecab ǰƤ뤥Ĥʣ ^~,~ӾA,*,*,*,*,,䥹,䥹 ~,һ,*,*,*,*,* ǰ ~,һ,*,*,*,*,ǰ,, ~,һ,*,*,*,*,* ~,*,*,*,Z?,ԽӾA,,, ~,һ,*,*,*,*,* ~,β,݄~Z,*,*,*,,ƥ,ƥ ~,*,*,*,?,ԽӾA,,, ӛ,һ,*,*,*,*,,, EOS ǰƤ뤥Ĥʣ? (ԥ20095º Ҋ) 7
- 8. ձZη2?N-gram gZgλǤϤʤօgλǷֽ OοͤϤ褯ʳͤ N=1 uni-gram N=2 bi-gram N=3 tri-gram ?Υ ?ΑBؽꥤǥåʤ O?ο?ͤ?Ϥ?褯??ʳ?ʳ??ͤ N=2 bi-gramΈ 8
- 9. 褯ȫė 쥹ݥXMLJSON ?Elasticsearch ?Apache Solr ?AWS CloudSearch ?InnoDB FTS (Full Text Search) ?mroonga MySQL٩`ʤ å ?`뤷䤹 ?ץ饰_kk ! ǥå ?RDBMSХåɤʥƥǤ ?Mzߴ å ?RDBMSХåɤʥƥ ?Mzߤ䤹 ! ǥå ?MySQLʹʤ ?`뤷ˤ9
- 10. MySQLLIKE LIKEǥǥåʹΤǰһ¤Τ LIKE ``% LIKE %``% LIKE %`` ǰһ gһ ᷽һ OK NG NG MyISAM(5.5ǰ)InnoDB FTS(5.6Խ)ʹʤޤ textͤΥ˥ǥåNʤ 10
- 12. Nȫė؏ 12 InnoDB FTS(Full Text Search) Tritonn mroonga MySQL-ftppc MySQL 5.0 MySQL 5.1 MySQL 5.6 MySQL 5.5 (My ISAMΤ) (My ISAMΤ)
- 13. MySQLʹNȫė 13 Tritonn mroonga MySQL-ftppc InnoDB FTS InnoDB MyISAM MeCab N-gram MySQL5.0 MySQL5.1 MySQL5.5 MySQL5.6 6 [gh] MySQLȫėTritonn顸mroongaؤХɣ1 http://gihyo.jp/dev/clip/01/groonga/0006?page=1
- 16. mroongaθҪ g\äȤƤϥǩ`hr˷֤ȤҪʤ CREATE TABLE dtb_products ( id INT PRIMARY KEY AUTO_INCREMENT, keyword VARCHAR(255), FULLTEXT INDEX (keyword) COMMENT 'parser "TokenUnigram"' ) ENGINE=mroonga comment = 'engine "innodb"'; INSERT INTO dtb_products VALUES(0, դϤݤǤ ) ! SELECT * FROM dtb_products MATCH(keyword) AGAINST( + IN BOOLEAN MODE) 16
- 17. Ʒ(mroonga) ?ȥ`mroonga ?``ɥˌ ?FULL TEXT INDEX Ʒ(InnoDB FTS) ?ȥ`InnoDB ?``ɥˌ ?FULL TEXT INDEX Ʒ(LIKE ) ?ȥ`InnoDB ?``ɥˌ ?ͨΥǥå 100(5000Ʒ 200n) 4(200ƥ` 200n) 100(5000Ʒ 200n) 100(5000Ʒ 200n) 100(5000Ʒ 200n) ¤ΥƩ`֥ˌSELECTUPDATEĤ 1،gФƥ٥ީ`gʩ ʹäƩ`֥ 17
- 18. ʹäh ` ߥɥ륦 AWSm1.mediumʹá ?CPU Intel(R) Xeon(R) CPU E5430 @ 2.66GHz 1 ?3.75GB ! CentOS 6.4 x86_64 ?MySQL5.6.12 ?mroonga4.0.0 18
- 19. SELECT Ʒ , FROM ƥ` LEFT JOIN Ʒƥ`(~) ON ƥ`.ƥ`ID=Ʒƥ`.ƥ`ID LEFT JOIN Ʒ USING(ƷID) WHERE Ʒ.ե饰=0 AND Ʒ.Ʃ`=1 AND Ʒ.`` LIKE %Z LIMIT 15 OFFSET 0 SELECT(LIKE) Zϥꚰ˥˩`Zʹ 19
- 20. SELECT Ʒ , FROM ƥ` LEFT JOIN Ʒƥ`(~) ON ƥ`.ƥ`ID=Ʒƥ`.ƥ`ID LEFT JOIN Ʒ USING(ƷID) WHERE Ʒ.ե饰=0 AND Ʒ.Ʃ`=1 AND MATCH(Ʒ.``) AGAINST( Z ) LIMIT 15 OFFSET 0 SELECT(mroonga/InnoDB FTS) Zϥꚰ˥˩`Zʹ 20
- 21. LIKEgһ InnoDB FTS mroonga(mecab) mroonga(Unigram) mroonga(Bigram) mroonga(Trigram) 10 100 5 16 27 9 19 3,000 14 21 39 17 31 2,000 ƽ 낎 SELECTgЕrg mroongaInnoDB FTSLIKEgһ˱Ȥ100ǰ١ mroongaNgramΈϡNۤɸ ߥ 100Υǩ`Ͷ뤷Ʃ`֥1SELECTg 21
- 23. LIKEgһ InnoDB FTS mroonga(mecab) mroonga(Unigram) mroonga(Bigram) mroonga(Trigram) 0 2.5 5 7.5 10 7 7 8 7 6 6 7 7 9 8 7 7 ƽ 낎 UPDATEgЕrg ߥ ɤʹäƤ⤽ۤɲo 100Υǩ`Ͷ뤷Ʃ`֥1UPDATEg 23
- 24. LIKEgһ InnoDB FTS mroonga(mecab) mroonga(Unigram) mroonga(Bigram) mroonga(Trigram) 10 100 1000 10000 291.45 210.82 208.69 201.01 6,106.13 75.8 100ǩ`indexrg InnoDB FTSW mroongaNgramΈNۤɸ¤˕rg줫 24
- 25. ݤˤʤäƤ Ʃ`֥ˤ¤ޤޤޤgroongaޤ 1ĤΥ`: 4096Bytes `ΥӋނ: 4GBytes ! gHˤTƼsˤӛޤǵ_ʤϤ⤢ޤ 25 5. ? Mroonga v4.01 documentation http://mroonga.org/ja/docs/reference/limitations.html