MySqlで巨大データのチューニング

現在作成してるものでメインデータ700万件超。
メインデータに紐づくタグデータ 6000万超。
と個人でなんとかするには無茶なデータ量。
当然、INDEXを張ったりなんだり。

覚えておく事として、mysqlは基本、1INDEXしか使わない。
複数のINDEXをしようして検索しませんよと。サブクエリーとかはまたそこでINDEXを使います。
1SQLで1INDEXというわけではないので注意。

で、当然こんだけあると遅い、遅い。
せめて1秒くらいで欲しいデータをだしたい。

そこで解析に使うのが[EXPLAIN]ですが、項目の簡単な見方。
あくまで簡易的であり私の私見なので間違ってたりするかもしれません。

table:
どのテーブルが対象なのかを表してます。

type:
検索パターンを示してます。ALLは基本的には無くす方向でチューニングします。
indexの場合には要注意くらいでも大丈夫かも。

possible_keys:
INDEX名が表示されます。

row:
実行するために必要だと判定したレコードの件数。
実際の件数ではないが、その件数を見る予定なので乱暴だが少ない方がいいと考えてよい。

Extra:
色々ありますが、Using filesort、Using temporaryが表示された場合は注意した方がいい。

MYSQLでキャッシュをつかわない。

mysqlで1回目は遅いけど同じSQLでは早い。 キャッシュを使ってるので当然といえば当然ですが、チューニングしてる場合にはキャッシュを使わずに計測したい。
いつも間違うのがこっち

SELECT NOCACHE col1,....

正しいのはこっち

SELECT SQL_NO_CACHE col1,...

というわけで備忘録です。

MySQLで縦列を横列にカンマ区切りにしたい。

MySQLで縦列を横列にカンマ区切りにしたいと思い色々検索してみる。 実際にはカラムにカンマ区切りで入れたいってのが要望です。 色々調べてみるが力技がほとんど。有ってもpivotが引っかかる。 どうも自分がしたい感じではない。 タグ表示に近い。 メインデータ

mainid	データ1	データ2
1	A	B

タグデータ

tagid	mainid	name
1	1	C#
2	1	MySQL
3	1	PHP

とDBに登録されているとして。こう表示させたい

id	データ1	データ2	タグ
1	A	B	C#,MySQL,PHP

色々調べた結果MySQLに便利な関数がありました。
GROUP_CONCATです。常識？今回の件で初めてしりましたよ。 以下、こんな感じで書き求めてる結果を出せました。

SELECT * 
FROM main 
  LEFT JOIN (SELECT 
      mainid,
      GROUP_CONCAT(name separator ',') AS タグ
      FROM tag
      GROUP BY mainid) tagd ON tagd.mainid = main.mainid