【MySQL】SITEN_CODE毎の高速ランキン

質問

MySQL5.7でランキングの集計をしたいのですが、遅すぎて困っています。

URIAGEテーブルのSITEN_CODE毎にKINGAKU_RANKとKENSU_RANKを集計したい。

SELECT
URIAGE.SITEN_CODE,
URIAGE.SHAIN_BANGO,
(SELECT
COUNT(URIAGE2.KINGAKU)
FROM URIAGE AS URIAGE2
WHERE URIAGE2.KINGAKU > URIAGE.KINGAKU AND URIAGE2.SITEN_CODE = URIAGE.SITEN_CODE)+1 AS KINGAKU_RANK,
URIAGE.KINGAKU,
(SELECT
COUNT(URIAGE2.KENSU)
FROM URIAGE AS URIAGE2
WHERE URIAGE2.KENSU > URIAGE.KENSU AND URIAGE2.SITEN_CODE = URIAGE.SITEN_CODE)+1 AS KENSU_RANK,
URIAGE.KENSU
FROM URIAGE

・件数が少ない内は動いたので、データ登録（約13万件）
・データ登録後、次のSQLで12時間動かしても終了しない。
という状態で困っています。

高速にランキング集計する方法があればご教授くださいm(_ _)m

回答No.1

mpro-gram

URIAGE　テーブルのカラム構成、index はどのようになってるのでしょう？？？
まずは(SITEN_CODE,SHAIN_BANGO) でuniqueでないと、select文に期間指定がないので、集計が不正確に思うけど。
KINGAKU　と　KENSU　とそれぞれで毎回比較が行われるので、こちらにもindexがないと、各行ごとに全件比較をおこなってようやくランクがでますから、件数の二乗回比較が行われます。十数万件の二乗ですから飛躍的に時間がかかるでしょう。
よって、(SITEN_CODE , KINGAKU) のindexと、(SITEN_CODE , KENSU) のindexが必要でしょう。
indexが多くなると今度はinsertやupdateでもいちいち時間がかかりますので、複数行編集するなら、その前にindexを無効化して、編集後index有効とするのがよいと思われます。indexの無効化は、MyIsamかinnodbかで異なるのでマニュアルご参照ください。
どこに出力してるのか知らないが全行出力するのにもメモリを喰うし時間取られるので、SITEN_CODEごとの出力にしてしまう手もありじゃあないのかな？