70万行くらいのMYSQLテーブルでSQL_CALC_FOUND_ROWSをやめたら高速化できた話

テーブル情報

• MYSQL：ver 5.6.42
• カラム数：14列
• データ数：おおよそ70万行
• インデックス：クエリに対しては適切に張っているつもり

改善前

SELECT SQL_CALC_FOUND_ROWS セレクト条件 FROM 色々JOIN WHERE インデクスの効く条件 LIMIT 0 30;

SELECT FOUND_ROWS() as count;

という感じでした。

この元々のクエリは、結果が多い場合だと1秒前後かかっていて、なんでそんなに重いんだろうと思っていました。

ちなみに、JOINは結構していますが、それぞれがしっかりとINDEXかPRIMARYで効くようにしているので、そこは原因ではないことは確認済み。

試しに、SQL_CALC_FOUND_ROWSをのぞいて、

SELECT セレクト条件 FROM 色々JOIN WHERE インデクスの効くと条件 LIMIT 0 30;

とすると、0.01秒くらいで結果が帰って来ます。

ここから「SQL_CALC_FOUND_ROWS」が重いのではないかと推測しました。

調べてみると、「SQL_CALC_FOUND_ROWS」は件数の大きいテーブルになるとパフォーマンスが落ちるという記事も見かけました。

改善後

「SQL_CALC_FOUND_ROWS」は、クエリ発行が楽なのはいいのですが、ユーザーへの応答が遅かったり無駄にDBに負荷をかけたりするのは無意味なので、「SQL_CALC_FOUND_ROWS」を無くして、同じ条件でcountするクエリを発行するようにしました。

SELECT SQL_CALC_FOUND_ROWS セレクト条件 FROM hoge 色々JOIN WHERE インデクスの効く条件 LIMIT 0 30;

SELECT count(id) as count FROM hoge WHERE インデクスの効く条件;

JOINは一覧への付加情報だったので、countの方ではのぞいています。

結果、APIからのコールで2秒くらいかかっていたのが、0.5秒まで高速化することができました。

全てのケースで「SQL_CALC_FOUND_ROWS」が悪という訳ではないですが、今回の自分のケースでは「SQL_CALC_FOUND_ROWS」を外すことで高速ができました。

データベースはテーブル構成や呼び出し方、データの量など様々な状況で変わってくるので、都度テストですね。