MySQLでcount(*)の結果がある一定数以下である場合にのみカウントしたい

はじめに

MySQLで、としていますが、他のRDBでも利用できるテクニックかもしれません。(未検証です)

何がしたい

SQLにおいて、特定の条件を指定したケースにおける実行結果の行数が10,000行を超えるかどうかなどを知りたい。
例：ある検索システムでは、結果セットが10000行を超えた場合には結果セットを返す必要はなく、追って絞り込み条件を追加してもらうようにしたい。（※どうせ画面上でチェックなんてできない）

何が問題？

普通にSELECT count(*)を実施するとクエリの内容によっては非常に長い時間がかかってしまう。
Webからユーザー指定の検索オプションで検索されるケースなどでこれをやられると簡単にタイムアウトしてまう。

通常のやり方

普通にSQLで絞込結果のカウントを行ないたい場合は、以下の構文を使います。

SELECT count(*) AS COUNT 
  FROM [TABLE] 
  WHERE [絞込条件] ;

しかし、これはこの結果行数が大きい場合には非常に遅くなります。（実際にSelect Queryで全部絞り込んでから律儀にカウントするため、元のデータの全スキャンになったり、結果セットのサイズに依存したりする。）

結果セットが10000行よりも大きいかどうかだけを知りたい場合にはあまり良くありません。

SELECT 時にSQL_CALC_FOUND_ROWSを指定する場合

MySQLでは、SQL_CALC_FOUND_ROWSを利用する事により、わざわざSELECT count(*) を別途コールしなくても、LIMIT/OFFSET句付きのクエリで実データを取得すると同時に、LIMIT句を指定しなかったときの全体数も取得可能となります。

一見スマートな方法に見えますが、この方法では最初のLIMIT/OFFSET付き結果取得クエリで全件Hitするまでスキャンしてしまい、応答が非常に遅くなります。（count(*)を使った場合と同じコストがかかる。）

LIMIT/OFFSET付き結果取得クエリ

SELECT SQL_CALC_FOUND_ROWS *  
  FROM [TABLE] 
  WHERE [絞込条件] 
  LIMIT 100 OFFSET 1000;

Limitをつけなかったときの行数取得クエリ

SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|      7452906 |
+--------------+
1 row in set (0.00 sec)

サブクエリでLimitをつけた結果をカウントする方法

以下の書き方にすると、Select Query部分も10,000行の結果セットが見つかった時点で応答を返答するので、SQLの実行時間を一定時間に押さえ込むことができます。

SELECT count(id) as COUNT 
FROM(
    SELECT 1 as id
      FROM [TABLE] 
      WHERE [絞込条件]
      LIMIT 10000
) as tmp;

このクエリを利用すると、

結果セット行数が10000行より小さい場合→結果行数を返答する
結果セット行数が10000行より大きい場合→10000を返答する

という挙動となります。

参考までに、テーブルサイズ1千万行、絞り込み結果行数百万行程度のクエリの場合、先のクエリで2分程度、後者のクエリで2秒程度の実行時間でした。

最後に

上記の他に、LIMIT/OFFSETによるページングは、SELECTで10,000件全部取得してから、スクリプト側でSliceするという方法もありますが、あまりにも富豪的プログラミング過ぎるので、ここでは割愛します。