スキップしてメイン コンテンツに移動

投稿

ラベル(SQL_CALC_FOUND_ROWS)が付いた投稿を表示しています

MySQLのSQL_CALC_FOUND_ROWS使用上の注意

MySQLのSQL_CALC_FOUND_ROWSを使用する際の無駄なメモリ消費に注意 ページング機能の実装などのために、ヒットした全件数を取得する必要のある場合があるかと思います。 その場合、下記のようにSQL_CALC_FOUND_ROWSを使うと、検索結果に加えて、そのクエリの全ヒット件数が取得できます。 SELECT SQL_CALC_FOUND_ROWS table.column1, table.column2 ...(途中省略)... FROM table WHERE (条件) しかし、SQL_CALC_FOUND_ROWSを使うと、「絞り込んだ結果にヒットする全べての行の結果のセットを作成する」という大きな欠点があります。 これは、LIMIT, OFFSETを指定していても実行されてしまうので、 SELECTで指定するカラム数やデータ量が多い SELECTの結果返ってくる行数が多い 場合、無駄に領域(≒メモリ)を消費してしまうので注意が必要です。 例えば、100万行検索対象としてヒットするクエリの場合、仮にLIMIT 20と指定して最初の20行を取得するようにクエリを書いても、その100万行分の結果セットが作成されてしまうということになります。 対応策 根本的な対応策としては、SQL_CALC_FOUND_ROWSを使わない(結果行数の取得はCOUNTを用いた別クエリで取得する、結果行数をあらかじめサマリーテーブルに保持しておく)ことですが、 SQL_CALC_FOUND_ROWSをどうしても使う必要がある場合は、 SELECT SQL_CALC_FOUND_ROWS primary_id のように最低限のカラムを指定して結果行セットを取得 (LIMIT OFFSET指定前提) 取得したprimary_idを使って必要なデータを取得 して、SQL_CALC_FOUND_ROWSで使用する領域をできるだけ減らすことで、対応するしかないと思います。 世の中ではLIMIT, OFFSETは使わない方がよいとよく書かれていますが、 SQL_CALC_FOUND_ROWSは、書いてしまえばどんなときも検索にヒットする全結果行セットを作成するので、同じくらい使用する際には注意が必要です。

Improve Performance for MySQL CALC_FOUND_ROWS with LIMIT OFFSET

SQL_CALC_FOUND_ROWS Performance Issue If you are working on MySQL (<=5.5) for a long time, you have an experience with performance issue on SQL_CALC_FOUND_ROWS with LIMIT OFFSET. The core cause is MySQL generates full result set as a temporary table even if user only requested first 20 rows in the query. This kind of situation often happens when you implement paging functionality. Please see following example. SELECT SQL_CALC_FOUND_ROWS tbl_a.a_id, tbl_b.*, tbl_c.*, FROM tbale_a AS tbl_a INNER JOIN table_b AS tbl_b ON tbl_b.a_id = tbl_a.a_id INNER JOIN table_c AS tbl_c ON tbl_c.b_id = tbl_b.b_id LEFT JOIN table_d AS tbl_d ON tbl_d.a_id = tbl_a.a_id WHERE tbl_b.field1 = "ABC" LIMIT 100, 20; In this example, a temporary table which has full result set is generated. If you configure SQL_CACHE, the second time query might be quite faster. However it is not a good choice because the query performance depends on status of tables, and not stable. Assume if tbl_b is fr