現在DBに1000万件ほどの簡単なデータを入れ色々とテストを行っているのですが、以下のような時、どうしても処理が遅くなってしまいます。これは「LIMIT」の仕様でしょうか。「id」は主キーで「hoge」はテーブル名です。
mysql> SELECT * FROM hoge ORDER BY id DESC LIMIT 9000,10;
10 rows in set (8.30 sec)
なお、オフセットの部分が0〜1000くらいでしたら問題ない速度です。
mysql> SELECT * FROM hoge ORDER BY id DESC LIMIT 0,10;
10 rows in set (0.00 sec)
mysql> SELECT * FROM hoge ORDER BY id DESC LIMIT 1000,10;
10 rows in set (0.05 sec)
9000から9010行目までの範囲をサクッと取り出せるのが理想なのですが、たった10行を取り出すのに実際には9010レコードを走査してしまっていているようです。これは仕方のないことなのでしょうか？何か良い方法があればご教授ください。実際のクエリーをお知らせ頂くと大変助かります。
なお、こんな記事も見つけましたが、出来ればデフォルトのMYSQLのみで何とかしたいと思います。。
http://qwik.jp/senna/old_mysql_binding_docs.html
以上、よろしく願い致します。
No.4
453
28
40pt
現在DBに1000万件ほどの簡単なデータを入れ色々とテストを行っているのですが、以下のような時、どうしても処理が遅くなってしまいます。これは「LIMIT」の仕様でしょうか。「id」は主キーで「hoge」はテーブル名です。
mysql> SELECT * FROM hoge ORDER BY id DESC LIMIT 9000,10;
10 rows in set (8.30 sec)
このテーブルは常時更新するものでしょうか、それともマスタテーブルのように割と長期間更新されないのでしょうか。
もし前者ならAUTO_INCREMENTのカラムが1からふられているのでそのINDEXをWHEREに使えばいいので簡単ですよね。
後者の場合、更新（削除・追加）のたびにidを付け直すという手があります。
つまりidのMAXイコールレコード数という状態にしておくということです。
>SELECT * FROM hoge WHERE id BETWEEN(9001,9010) ORDER BY id DESC ;
ならすぐ返ってきますよね。
他に、1000万件というレコード数を考えた場合、テーブルの水平分割が考えられると思います。例えば、hogeを100のテーブルに分割して1テーブル約10万レコードにすれば、上のクエリは1番目のテーブルしかソートする必要はないということです。「非正規化」「水平分割」などのキーワードで書籍などを調べればもっといい方法があると思います。
さらに、テーブルを作らなくてもレコード数で区切ってVIEWを作っておくのもありだと思います。
No.1
721
23pt
テーブルの構造を見ていないので、なんとも言えませんが、
テーブル作成の際に、インデックスを作成するとレコード数が増えた場合、速度向上が期待できます。
詳しくは下記URLにて。
ご回答ありがとう御座います。
テーブルは以下のような感じで作成しました。
$sql = 'CREATE TABLE hoge (
id INT NOT NULL AUTO_INCREMENT,
aaa CHAR(16) NOT NULL,
bbb INT(4) NOT NULL,
ccc INT NOT NULL,
PRIMARY KEY (id),
)TYPE = myisam;';
idが主キー(インデックス)になっていて、その点は大丈夫かと思います。
No.2
52361
28pt
>これは仕方のないことなのでしょうか？何か良い方法があればご教授ください。実際のクエリーをお知らせ頂くと大変助かります。
仕方のないことだと思います。 というのは、この場合、ORDER BYを利用しているわけですが、LIMIT x,yにおいて、xの位置はORDER BYによりソートした結果になるので、レコードの開始位置までソート処理しなければxが確定できない為です。
EXPLAINで問い合わせを確認してみたらいかがでしょうか。
ご回答ありがとう御座います。
なるほど、やはり仕方ないことなのですね、、
No.3
2915301
30pt
■仕様
SELECT * FROM hoge ORDER BY id DESC
の返してきた結果セットに対して LIMIT が働く。
↓
SELECT * FROM hoge ORDER BY id DESC
までの部分ではインデックスの有無で処理速度が大きく変わるけれども
LIMITの時点ではインデックスの有無は既に関係無い状態。
■対処
WHEREで結果セットを少なくするしかないでしょう。idを利用して絞り込む方向で考えてみてください。
データの抹消などもあってidが一部欠如する場合もあるかとは思いますが、
その部分はイレギュラー処理（結果がゼロ件ならば再検索など）するようなロジックを組んで対処すれば良いでしょう
（イレギュラー処理を行っても8.3secよりは十分に高速だと割り切るしかない）
ご回答ありがとう御座います。
なるほど、そのような内部処理なのですか、勉強になります。
対処方も大変参考になりました。ようは工夫ですね。もう少し色々考えてみたいと思います。
No.4
45328
ここでベストアンサー
40pt
現在DBに1000万件ほどの簡単なデータを入れ色々とテストを行っているのですが、以下のような時、どうしても処理が遅くなってしまいます。これは「LIMIT」の仕様でしょうか。「id」は主キーで「hoge」はテーブル名です。
mysql> SELECT * FROM hoge ORDER BY id DESC LIMIT 9000,10;
10 rows in set (8.30 sec)
このテーブルは常時更新するものでしょうか、それともマスタテーブルのように割と長期間更新されないのでしょうか。
もし前者ならAUTO_INCREMENTのカラムが1からふられているのでそのINDEXをWHEREに使えばいいので簡単ですよね。
後者の場合、更新（削除・追加）のたびにidを付け直すという手があります。
つまりidのMAXイコールレコード数という状態にしておくということです。
>SELECT * FROM hoge WHERE id BETWEEN(9001,9010) ORDER BY id DESC ;
ならすぐ返ってきますよね。
他に、1000万件というレコード数を考えた場合、テーブルの水平分割が考えられると思います。例えば、hogeを100のテーブルに分割して1テーブル約10万レコードにすれば、上のクエリは1番目のテーブルしかソートする必要はないということです。「非正規化」「水平分割」などのキーワードで書籍などを調べればもっといい方法があると思います。
さらに、テーブルを作らなくてもレコード数で区切ってVIEWを作っておくのもありだと思います。
ご回答ありがとう御座います。
テーブルはアプリケーションで言うところの「掲示板」で使用しております。普通に記事ナンバーや発言を記録しております。
なるほど！いろんなやり方があるのですね。大変参考になりました。まだ私の方のレベルが低すぎてYotaさんの仰ることが100％理解出来ておりませんが、BETWEENを使ったテストでサクッと取り出すことが出来ました！目からウロコです。
BETWEEN(9001,9010)の、開始番号と終了番号の取得を確定出来れば、やりたいことが実現出来そうです。※実際には番号が欠番になることがあるので、、
もう少し詳しく説明いたしますと、掲示板で次のページへという処理を行っております。アプリ側は..hoge.php?page=100などの感じです。
取り急ぎお礼申し上げます。引き続き色々と試行錯誤してみたいと思います。
この度はありがとう御座いました。
kurukuru-neko
2007/05/14 23:10:41
1
1
1
1
1
12
12
uniuniko
kn1967
standard_one
ご回答ありがとう御座います。
テーブルはアプリケーションで言うところの「掲示板」で使用しております。普通に記事ナンバーや発言を記録しております。
なるほど！いろんなやり方があるのですね。大変参考になりました。まだ私の方のレベルが低すぎてYotaさんの仰ることが100％理解出来ておりませんが、BETWEENを使ったテストでサクッと取り出すことが出来ました！目からウロコです。
BETWEEN(9001,9010)の、開始番号と終了番号の取得を確定出来れば、やりたいことが実現出来そうです。※実際には番号が欠番になることがあるので、、
もう少し詳しく説明いたしますと、掲示板で次のページへという処理を行っております。アプリ側は..hoge.php?page=100などの感じです。
取り急ぎお礼申し上げます。引き続き色々と試行錯誤してみたいと思います。
この度はありがとう御座いました。