検索結果を指定件数ずつ抽出表示したい。

データベース（以下、DB）は、SQLite2です。
PHPスクリプトの中で利用しています。

なお、本質問はMySQLとして説明して頂いても結構です。
（恐らく、同じような方法で解決できると思いますので。）

考え方が分かりましたら、あとは自分で調べられますので、
その方向性だけを示して下さるというのでも結構です。

さて、質問に移らせて頂きますが、

DBに検索をかけ、ヒットしたレコードを、
ある特定のカラムをもとにソートし、その中の上から指定件数分抽出する
ということをしたいと考えています。

例をあげます。

テーブル名（tbl_siritori）

id word cate
1 りんご 食べ物
2 ごりら 動物
3 らくだ 動物
4 ダック 動物
5 くり 食べ物
6 リス 動物
7 スイカ 食べ物
8 カメ 動物
9 メロン 食べ物

というDBがあったとして、

このDBに、[cate]に「食」という文字が入っているようなデータを、
[id]順に見て、大きな数字のものを上から３件抽出したい場合、
どのような方法（SQL文）が考えられますか。

３件のデータを表示した後、
さらにそれに続く３件のデータを次ページに表示、
さらにそれに続く３件も同様にさらに次ページに表示…（略）

ということをしようと思っています。

１ページに３件、で、何ページにも渡ってデータを３件ずつ出力していく、ということです。

以上のことを実現可能であるSQL文の中でも、
最も一般的なものを教えて下さい。

$query =
"SELECT * FROM tbl_siritori WHERE (cate LIKE '%食%') ～～ごにゃごにゃ～～ ORDER BY id DESC";

というような書き方で、１文で解決できるものと思っていますが、
複数の文で絞り込むような形になるのでしょうか。

以上、よろしくお願いいたします。

No.3 ベストアンサー 回答者： chukenkenkou 回答日時： 2009/06/15 18:17

LIMIT [x,]y

あるいは
LIMIT y OFFSET x

検索条件で絞り込まれ、ORDER BYでソートした結果のx+1行目（先頭は0）からy行を得るという指定です。
ORDER BYがないとMySQLは結果を返す順を保証しないので、LIMITを使用する場合は、通常は必ずORDER BYと組み合わせて使用します。
また、検索条件とORDER BYの組み合わせ等でインデクスを有効利用できれば、インデクス上で「x+1番目からy行」を見つけられるので、性能が極端に落ちません。しかし、逆にインデクスでなく母体データをサーチしなければならないような場合は、母体データ件数が多いと、OFFSETで検索対象をスキップするのに大幅に性能劣化するので注意しましょう。

select ～
from t1
[where 検索条件]
ORDER BY ソートの指定
LIMIT 0,3

とした場合、該当する行の先頭（OFFSET 0）から3行が検索結果として得られます。

select ～
from t1
[where 検索条件]
ORDER BY ソートの指定
LIMIT 3,3

とした場合、4行目（OFFSET 3）から3行が検索結果として得られます。

さらに、7行目からは LIMIT 6,3 といったように指定します。

SELECT文の構文については、下記参照。
http://dev.mysql.com/doc/refman/5.1/ja/select.html

どういった検索条件やORDER BYの指定だと、インデクスを効率的に使用できるかは、下記参照。ただし、MySQLの話しです。
http://dev.mysql.com/doc/refman/5.1/ja/query-spe …

この回答への補足

さらなるアドバイスをありがとうございます！

select ～
from t1
[where 検索条件]
ORDER BY ソートの指定
LIMIT 0,3

これで、出来ました！
まさに、このまんまで！
（ちなみに、indexは今のところ使用していません）

とても助かりました。
出来ないものと思っていたので、嬉しいです～。＾＾
どうもありがとうございました！

補足日時：2009/06/15 20:36

この回答へのお礼

ありがとうございました（＾＾）
また何かありましたら、教えて下さいね。

お礼日時：2009/07/10 23:22

No.4 回答者： chukenkenkou 回答日時： 2009/06/15 18:23

#3の文言を、一部訂正します。

＜訂正前＞
母体データ件数が多いと、OFFSETで検索対象をスキップするのに大幅に性能劣化する

＜訂正後＞
母体データ件数が多かったり、検索対象行が多いと、ORDER BYでのソート及びOFFSETで検索対象をスキップするのに大幅に性能劣化する

この回答への補足

了解でしっ！

補足日時：2009/06/15 20:40

この回答へのお礼

ありがとうございました（＾＾）
また何かありましたら、教えて下さいね。

お礼日時：2009/07/10 23:21

No.2 回答者： chukenkenkou 回答日時： 2009/06/15 15:03

SELECT文で、LIMIT句、あるいは「LIMIT句とOFFSET句」を組み合わせればいいのでは？

この回答への補足

簡潔なアドバイスをありがとうございます。笑

＞「LIMIT句とOFFSET句」を組み合わせればいいのでは？

OFFSET句というのが、どのようなものか知らなかったので、ざっくり調べてみました。
まだよく分かりませんが、スキップができるようですね。

このスキップがどう利用価値のあるものなのか、また、その使い方について分からないので、もしよろしければ教えて頂けると大変ありがたいです。
もちろん自分でもさらに調べてみますが、、。笑

ちょっと希望の光が見えてきたような気がします。
今後の展開が楽しみです。

補足日時：2009/06/15 15:07

この回答へのお礼

ありがとうございました（＾＾）
また何かありましたら、教えて下さいね。

お礼日時：2009/07/10 23:22

No.1 回答者： mako_tyo 回答日時： 2009/06/15 14:00

私も、同じようなことをやろうとしましたが。

。。
見つかりませんでした。

top、not in、unionなど試しました。

Oracleなら簡単な方法があったのですが・・・　orz

私の解決策としては、SQL文で処理するのではなく、プログラムで処理しました。

この回答への補足

どうもです、mako_tyoさん。

＞私も、同じようなことをやろうとしましたが。。。

とのことなので、恐らく、ご自身で検索はもちろんのこと、質問の投稿までされたのだと思います。
そして、このような回答をして頂いたのだと思います。
恐れ入ります。

＞top、not in、unionなど試しました。

私はまだ何も動き出していない状態なので、ひとまず、
上記の方法　top,not in,union　には近づかないようにします。（笑）

＞Oracleなら簡単な方法があったのですが・・・　orz

オラクルについては、知識０なので、どうにもこうにも・・・ora　(オラクルバージョンorz)

＞私の解決策としては、SQL文で処理するのではなく、プログラムで処理しました。

最終手段である北斗神拳を使われてしまったわけですね。
私も同じ道を辿ることになるのだろうか・・・

ちょっとふざけすぎましたが、真面目に文章を打ち込んでいますので、
怒らないで下さいね！笑

おそらく、プログラムで処理するとなると、
まず、抽出したいものを通常通り抽出し、それらを
１，新たにDBを用意してそれにぶちこんで、そこでソートして抽出するか、
２，配列か何かにぶち込んで、同様にソートして抽出するか、

そのような方法になるのでしょうね。
１の方法は、よほど複雑な場合を除き、避けたいですね。

というか、プログラムで処理する場合でも、
上に挙げた１，２の方法よりも、もっと良い方法がありそうですよねｗ

それについて、新たに質問すべきなのかしら。

頻繁にする検索動作だけに、やはり、それなりに効率的なものを、
最初の段階でしっかり設計しておきたいですからね。

もし余力がありましたら、最終的にどう決着されたのか、
その方法について教えて頂けると嬉しいです。

補足日時：2009/06/15 15:04

この回答へのお礼

ありがとうございました（＾＾）
また何かありましたら、教えて下さいね。

お礼日時：2009/07/10 23:22