limit最適化テスト

userテーブルには37件のレコードがあります
1.select*where条件なし

mysql> explain select * from user limit 29,7 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 37
        Extra:

2.select*where条件あり

mysql> explain select * from user where id > 29 limit 0,7 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 8
        Extra: Using where

3.select col_nameにwhere条件がある

mysql> explain select age from user where id > 29 limit 0,7 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 8
        Extra: Using where

実際のクエリの所要時間の比較

mysql> select * from buy_keywords limit 50000,10;
// 
10 rows in set (0.16 sec)

mysql> select * from buy_keywords where id > 50000 limit 0,10;
// 
10 rows in set (0.03 sec)