GOのDBを，PostgreSQLとMySQLで作成してみる

bioinformatics computer

「http://d.hatena.ne.jp/sesejun/20060805」を書いた際に，PostgreSQLのDBにGO(Gene Ontology Resource)のデータを入れるのが，遅すぎる感じがしたので，MySQLで試してみた．
MacBook Pro 上で，PostgreSQLを使ってhttp://www.se-se.jp/doc/go-postgres.htmlの通りに作成すると，約1.5時間かかりましたが，MySQLのダンプからのリストアでは，4分弱（！）．この差はなに・・・．以下手順．

$ sudo port install mysql5 +server

  • my.cnf でMySQLの文字コードを一応UTF-8に編集
    • /opt/local/etc/mysql5/my.cnf を作成し，以下の内容を書き込む

[mysqld]
default-character-set=utf8

[mysql.server]
user=mysql5
default-character-set=utf8

[mysqld_safe]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqldump]
default-character-set=utf8

  • DBの初期設定，Macの設定＆MySQL起動

$ sudo -u mysql mysql_install_db5
$ sudo launchctl load -w /Library/LaunchDaemons/org.darwinports.mysql5.plist
$ sudo /opt/local/share/mysql5/mysql/mysql.server start

  • MySQLにユーザを作成

$ mysql5 -u root
mysql> GRANT ALL ON *.* TO username@localhost IDENTIFIED BY 'パスワード';
mysql> flush privileges;
mysql> exit

  • goという名前のDBを作成

$ mysql5 -u username -p
password: パスワード
mysql> create database go;

  • go のMySQLのダンプをダウンロード して，展開．

$ curl -O http://archive.godatabase.org/latest-lite/go_20060730-assocdb-data.gz
$ gzip -d go_20060730-assocdb-data.gz

  • goのデータをDBにつっこむ

$ mysql5 -u username -p go < go_20060730-assocdb-data

最後の，DBへのリストアでかかった時間は，4分弱・・・．
なんだ，このポスグレとの時間差は・・・．
（理由の一つは，ポスグレではtab区切りのテーブルで入れたのに対し，MySQLではMySQLの解釈しやすいSQLで書かれている事，もう一つは，データを入れるときにPostgreSQLはデータを入れるたびにキーの制約を調べているのにたいし，MySQLは外部キー制約を満たしているかは，見ないでデータをつっこみ，最後に調べる（調べてない？）という戦略をとっていることは考えられますが・・・差が激しいな）

因にhttp://www.godatabase.org/dev/sql/doc/example-queries.htmlにある，以下のSQLを実行すると，MySQLでは4分前後(2回行って3分40秒と4分20秒)，PostgreSQLでは1分20秒（2回計測）でした．複雑なqueryが多い場合には，PostgreSQLにつっこむ価値があるかな？

SELECT
t2.name, t2.acc, count(distinct a2.gene_product_id) AS gpc
FROM
term t1,
term t2,
graph_path p1,
graph_path p2,
association a1,
association a2,
evidence e1,
evidence e2
WHERE
t1.id = p1.term1_id AND
t2.id = p2.term1_id AND
a1.term_id = p1.term2_id AND
a2.term_id = p2.term2_id AND
e1.association_id = a1.id AND
e2.association_id = a2.id AND
a1.is_not = 0 AND
a2.is_not = 0 AND
e1.code != 'IEA' AND
e2.code != 'IEA' AND
a1.gene_product_id = a2.gene_product_id AND
t1.acc = 'GO:0003700' AND
a1.term_id != a2.term_id
GROUP BY t2.name, t2.acc
ORDER BY gpc DESC
LIMIT 30