またもやMySQLで完全にハマったのでメモ。
症状
- 投げたクエリが、帰ってくる気配が無い。
- process list を見ると、当該のクエリは "Copying to tmp table" と言っている。
- 当該クエリから ORDER BY 句を除いて実行すると、応答が無いのは同じだが、process listの表記が "Sending data" と言うようになる。
調査その１：tmp_table_sizeとmax_heap_table_size
"Copying to tmp table" でググったときにまず出てくるのが、これらのパラメータを変更しましょう！という情報。
mysql - "Copying to tmp table" extremely slow - Database Administrators
tmp data を格納する領域が十分でなければ、そりゃあcopyも莫大な時間がかかりますよね、という理屈。
もちろん試してみましたが、僕が遭遇した環境では効果無し。
調査その２：MySQL再起動
困ったときの奥の手。再起動。
試しても何も変わらず。
調査その３：サーバログ
mysqldが吐いてるログを見ても、手がかりは見つからず。
調査その４：当該クエリをexplain
この調査、再起動よりも先にやれよｗｗという野次が飛んできそうですが、僕の精神的余裕の無さから順序が入れ替わってしまいました。
しかし、explainの結果を見ても、特に異常な点（rowsが異常に多いとか）が見当たらず。
調査その５：当該indexを調査
explainで示されたindexの状況を見てみると、なんとcardinalityが0になっているではありませんか！！
もう少し詳しく言うと、Primary KeyのIndexのみは適切な値が出力されていて、それ以外のIndexのcardinalityが0の状態でした。
analyze table をかけたら、cardinalityには適切な値が入り、クエリも妥当な時間で結果を返すようになりました。
※注（追記＠2012.12.06）：phpMyAdminで見た時のcardinalityが0だということです。phpMyAdminのバージョンによっては見え方が違うかもしれません。実際のcardinalityは null です。
クエリの実行中に、何が起きているのか？（推測）
何らかの理由で統計情報が壊れた結果、クエリの実行中に optimizer さんが無限ループにでも陥ってしまったのでしょう（でも、この推測はクエリからORDER BY句を除いたときの症状と矛盾するような気もします。追加の調査や勉強が必要。。）。
なぜ統計情報が壊れたのか？
このQ&A MySQL MyISAM table index cardinality is zero - Stack Overflow の回答者さんがMySQLマニュアルから引用されている通り、データをロードした後は analyze table を実行すべきです。これはMySQLに限らず、他のRDBMSでも同じ事が言えます。
今回、僕が遭遇したサーバでは、実際にデータのロードを実施していました。
そう言えばデータのロードをする前は、当該のクエリも元気に動いていたなーというのを思い出しました。
統計情報が壊れる原因としては、データのロードの可能性が高いな、と思います（でも断定はできず）。
cardinalityがnullになっているindexを抽出
このSQLを時々流せば、幸せになれるかも？！（※実行は自己責任で。）
select table_schema, table_name, index_name from information_schema.statistics where table_schema = 'DB名' and cardinality is null group by table_schema, table_name, index_name order by table_schema, table_name, index_name
データが0件じゃないテーブルがこのリストに出てきたら危険信号、と考えても良いのではないでしょうか。
