勉強したことのメモ

webプログラマ見習いが勉強したことのメモ。

MySQLでJOINの高速化

      2014/05/22

JOINで無茶苦茶悩んだのでメモ。
やりたかった事は、MySQL

・テーブルAには必要最低限のログが格納
・テーブルBにはカテゴライズ情報が格納
・AとBを組み合わせてカテゴリー毎に集計したい

というもの。

あと難点として、テーブルAは200万件ほどデータが入っていて、
尚且つサーバースペックが5年以上前のものらしくあまりよくないらしい。

で、最初に考えた案としては普通にJOINして、
結果をWHEREで絞って集計。

SELECT SUM(aaa)
FROM table_a AS a
LEFT JOIN table_b AS b
ON a.aaa = b.bbb
WHERE ~~~

みたいな感じ。結合のキーは文字列。

1日毎の集計だと問題ないけど月間、年間で集計したら
トラフィックがえげつないことになった。
かといってPHPで処理したらメモリ不足でタイムアウトが発生した。

上司に相談したところ、DBの構成を考え直した方が良いとの回答。
どうしようもないなら下記みたいにしてみたら、とのこと。

SELECT sum(aaa)
FROM (
SELECT aaa
FROM table_a
WHERE ~~~ //条件絞って件数減らす
) AS a
LEFT JOIN (
SELECT bbb
FROM table_b
WHERE
) AS b
ON a.aaa = b.bbb

結合する際は、各々の件数を減らした上で結合するといいらしい。
試してみると確かに高速化になった。

ただ、これでもトラフィックがあれだったのでDBの構成を考え直すことに。
1番はAテーブルにカテゴライズ情報を一緒に格納することだけど、
色々事情があってそれはしたくなかった。

妥協案としてJOIN用のテーブルを作って解決できそう。
要点はプライマリのカラムを結合させる。

idとかseqとかautoincrementに設定するようなものを
結合キーにすると速度が出た。

今後も同じことがあると思うので覚えておく。

 - MySQL

  関連記事

msyql-image
MySQLで開始から終了日付内を調べたいとき

等号不等号でもできるけど、BETWEENというのがよさそう。 ■参考サイト ht ...

msyql-image
MySQLで実行結果の行数取得

DBにデータがあるか調べる際にしばしば使用してたのでメモ。 $sql = " S ...

msyql-image
MySQLで経度緯度から半径○Kmのデータを抽出

経度と緯度をtext型として格納しているMySQLのテーブルがあり、指定した経度 ...

msyql-image
MySQLでREGEXPについて

SQL文にREGEXPというのを見てこれ何? となったのでメモ。 ■参考サイト ...

images
PHPからMySQLにシリアライズしたデータを格納

アンケートフォームみたいなのがあって、 今後も定期開催し、尚且つ質問内容は増減す ...

msyql-image
MySQLのintのカラムで2147483647の値

MySQLでintのカラムに数値を入れると「2147483647」という値になっ ...

msyql-image
InnoDBのオートインクリメント

MySQLのInnoDBでIDというカラムにオートインクリメント設定していたが、 ...

msyql-image
MySQLでホスト指定しても正常に接続できない場合

MySQL自体は動いており、ユーザーも登録されているのに、ホスト名を指定しても正 ...

images
MySQL/Apacheの再起動

忘れないようにメモ。 ■MySQL service mysqld restart ...

msyql-image
MySQLでカラム数の異なるテーブルを結合する(UNION)

MySQLでカラム数の異なるテーブル同士をUNIONで結合させたかった。ただ、何 ...