技術トピック 2007年9月24日: SQLに関する長年の疑問
【ソリューション事業部 タケシ】
SQLと言うかPostgreSQL or MySQLに関して６、７年疑問に思っていることがあったのですが
先日とある携帯サイトの仕事をした時にソースを眺めていて気づいたので書いときます。
検索結果ページなんかで「該当した1013件中201件目から250件目を表示」
のように表示する事がよくあるのですが、DBからスクリプト(PHP)へ該当した
全件のデータを受け渡しすると該当件数が多くなるとメモリをはじめとする
各種リソースを食いつぶしてしまうので、limit句OFFSET句やなどを使って
201件目から250件目の50件分のみを取得するのが一般的な検索となります。
SELECT * FROM ITEM WHERE ITEM_NAME LIKE ‘%シグマ%’ OFFSET 200 limit 50;
ところがこの方法では1013件該当した事がわからないので
SELECT COUNT(*) FROM ITEM WHERE ITEM_NAME LIKE ‘%シグマ%’;
と、もう一度同じ条件の該当件数を取得だけのSQLを再度実行することになります。
PostgreSQLの場合一般的には皆さんこんな感じで処理しているみたいなのですが、
例の様なLIKE句を使用した部分一致検索の場合、インデクスが利用できないので、
該当件数を取得するためだけに、数万件のレコードに対して処理するような負荷のかかる検索を
2回も実行するのはどうも納得がいかないでいたんです。
で、強引にあみだしたのがカーソルを使って検索結果を保存しておいて
MOVE ALL FROM CURSOR_NAME;
を実行してこの結果から該当件数を取得する方法です。
一応この方法で少しのオーバーヘッドで無駄な検索をなくす事ができるのですが
少しスマートじゃないなと思っていた今日この頃、
某キャラクターの携帯コンテンツのお仕事をしていた時
MySQLのクエリを眺めていたらSQL_CALC_FOUND_ROWSとfound_rows()を発見！！
これを使うと１回の検索で該当件数も取得できるので私の気にしていた無駄な検索もなし。超スマート！
トランザクションが使えないレンタルサーバで構築しろと言われたり
タイムゾーンの対応ができない海外サーバに出くわしたり、
自分から進んでMySQLを使用しようとは思わないのですが、
初めてMySQLが便利だと思った機能でした。
で、SQL_CALC_FOUND_ROWSとfound_rows()をキーワードに
PostgreSQLの同等の機能を検索してみたのですが、やはりない模様。
とりあえずはカーソルで我慢する事にします。
因みに昨日３１歳になりました。
昨日は第２子出産から退院したばかりの嫁さんと
生後１週間のまだ名前が決まっていない次男と
お母さんの入院中各所に預けられてまくって熱が出てなかなか治らない長男と
4人で嫁さんの実家でおとなしくすごしました。
同じお誕生日の方は
歴代ローマ皇帝アウグストゥス
稲葉 浩志
後藤 真希
叶 美香
イジリー岡田。。。
http://www.d4.dion.ne.jp/~warapon/data00/birth-0923.htm
