磯部 和広
k-iso****@rozet*****
2013年 7月 1日 (月) 16:27:47 JST
いつもお世話になっております。
Q1の回答、ありがとうございました。
Q2に関しましては、肝心のDDLとデータを開示できないので
本質的な部分のみのDDLを開示します。
元のCSVテーブル
CREATE TABLE `TAIYAKU_DATA` (
`id` int(11) NOT NULL ,
`EN_TEXT` text NOT NULL,
`JA_TEXT` text NOT NULL,
) ENGINE=CSV DEFAULT CHARSET=utf8;
mroonga化SQL
alter table TAIYAKU_DATA
modify column id int(11) NOT NULL AUTO_INCREMENT,
add PRIMARY KEY (id),
add FULLTEXT KEY EN_TEXT (EN_TEXT),
add FULLTEXT KEY JA_TEXT (JA_TEXT),
ENGINE=mroonga DEFAULT CHARSET=utf8;
問い合わせ用SQL
"select count(ID) as counts from TAIYAKU_DATA where match(JA_TEXT)
against ('\"" + searchText + "\"' in boolean mode)"
"select count(ID) as counts from TAIYAKU_DATA where match(EN_TEXT)
against ('\"" + searchText + "\"' in boolean mode)"
my.cnfは下記を使用しています。（※32GBメモリマシン用です）
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8
[mysqld]
symbolic-links=1
mroonga_database_path_prefix = "mysql/mroonga.data/"
mroonga_match_escalation_threshold = -1
mroonga_log_level = "ERROR"
skip-name-resolve
innodb_file_per_table
innodb_log_buffer_size = 100M
innodb_buffer_pool_size = 24G
port = 3306
socket = /tmp/mysql.sock
datadir = /var/lib/mysql
key_buffer = 2048M
max_allowed_packet = 160M
table_cache = 5120
table_open_cache = 5120
sort_buffer_size = 20M
read_buffer_size = 20M
read_rnd_buffer_size = 800M
myisam_sort_buffer_size = 1024M
thread_cache_size = 800
query_cache_size = 160M
max_connections = 500
thread_concurrency = 16
innodb_flush_method=O_DIRECT
max_heap_table_size = 5G
[mysqldump]
quick
max_allowed_packet = 16M
default-character-set = utf8
[mysql]
no-auto-rehash
default-character-set = utf8
[isamchk]
key_buffer = 1280M
sort_buffer_size = 1280M
read_buffer = 200M
write_buffer = 200M
[myisamchk]
key_buffer = 1280M
sort_buffer_size = 1280M
read_buffer = 200M
write_buffer = 200M
[mysqlhotcopy]
interactive-timeout
lo-error=/var/log/mysqld.log