EXPLAIN SELECT ID,OrderID,UserID,ContactEmail,PaymentCurrencyID,ShippingPrice,TotalPrice,
TransactionID,HasHardware,OrderStatus,DeliveryStatus,CreateDate,SendMailStatus 
from `order_list` where 1=1  and PaymentCurrencyID = 2 and CreateDate>='1999-09-09' and CreateDate <='2011-01-11' Order By CreateDate DESC LIMIT 140194,100 ;出现的结果:能使用索引.但是只要把LIMIT 改成 140195,100.就无法使用索引.难道索引在这上面也是有限制的吗?

解决方案 »

  1.   

    SHOW INDEX FROM tbl_name 
    贴结果
      

  2.   

    mysql> SHOW INDEX FROM order_list;
    +------------+------------+------------+--------------+-------------------+-----
    ------+-------------+----------+--------+------+------------+---------+
    | Table      | Non_unique | Key_name   | Seq_in_index | Column_name       | Coll
    ation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +------------+------------+------------+--------------+-------------------+-----
    ------+-------------+----------+--------+------+------------+---------+
    | order_list |          0 | PRIMARY    |            1 | ID                | A
          |      280590 |     NULL | NULL   |      | BTREE      |         |
    | order_list |          1 | CreateDate |            1 | CreateDate        | A
          |          18 |     NULL | NULL   |      | BTREE      |         |
    | order_list |          1 | CreateDate |            2 | OrderID           | A
          |      280590 |     NULL | NULL   |      | BTREE      |         |
    | order_list |          1 | CreateDate |            3 | ContactEmail      | A
          |      280590 |     NULL | NULL   |      | BTREE      |         |
    | order_list |          1 | CreateDate |            4 | UserID            | A
          |      280590 |     NULL | NULL   |      | BTREE      |         |
    | order_list |          1 | CreateDate |            5 | PaymentCurrencyID | A
          |      280590 |     NULL | NULL   |      | BTREE      |         |
    +------------+------------+------------+--------------+-------------------+-----
    ------+-------------+----------+--------+------+------------+---------+
    6 rows in set (0.09 sec)mysql>
      

  3.   

    Enter password: ****
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 28
    Server version: 5.1.34-community MySQL Community Server (GPL)Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use bluesoleil_en
    Database changed
    mysql> show index form order_List;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
    corresponds to your MySQL server version for the right syntax to use near 'form
    order_List' at line 1
    mysql> SHOW INDEX FROM order_list;
    +------------+------------+------------+--------------+-------------------+-----
    ------+-------------+----------+--------+------+------------+---------+
    | Table      | Non_unique | Key_name   | Seq_in_index | Column_name       | Coll
    ation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +------------+------------+------------+--------------+-------------------+-----
    ------+-------------+----------+--------+------+------------+---------+
    | order_list |          0 | PRIMARY    |            1 | ID                | A
          |      280590 |     NULL | NULL   |      | BTREE      |         |
    | order_list |          1 | CreateDate |            1 | CreateDate        | A
          |          18 |     NULL | NULL   |      | BTREE      |         |
    | order_list |          1 | CreateDate |            2 | OrderID           | A
          |      280590 |     NULL | NULL   |      | BTREE      |         |
    | order_list |          1 | CreateDate |            3 | ContactEmail      | A
          |      280590 |     NULL | NULL   |      | BTREE      |         |
    | order_list |          1 | CreateDate |            4 | UserID            | A
          |      280590 |     NULL | NULL   |      | BTREE      |         |
    | order_list |          1 | CreateDate |            5 | PaymentCurrencyID | A
          |      280590 |     NULL | NULL   |      | BTREE      |         |
    +------------+------------+------------+--------------+-------------------+-----
    ------+-------------+----------+--------+------+------------+---------+
    6 rows in set (0.09 sec)mysql>
      

  4.   

    总共 280590 条记录,你需要取 140194条记录,MYSQL认为没必要走索引了。
      

  5.   

    楼主该下查询:
    SELECT ID,OrderID,UserID,ContactEmail,PaymentCurrencyID,ShippingPrice,TotalPrice,
    TransactionID,HasHardware,OrderStatus,DeliveryStatus,CreateDate,SendMailStatus 
    from `order_list`
    inner join
    (
    select ID from order_list where 1=1  and PaymentCurrencyID = 2 and CreateDate>='1999-09-09' and CreateDate <='2011-01-11' 
    Order By CreateDate DESC LIMIT 140194,100 
    ) as pp using(ID);
    再把这个索引加上
    alter table add index aaaa on (CreateDate,PaymentCurrencyID)explain 看看什么结果
      

  6.   

    mysql> explain SELECT ID,OrderID,UserID,ContactEmail,PaymentCurrencyID,ShippingP
    rice,TotalPrice,
        -> TransactionID,HasHardware,OrderStatus,DeliveryStatus,CreateDate,SendMailS
    tatus
        -> from `order_list`
        -> inner join
        -> (
        -> select ID from order_list where 1=1 and PaymentCurrencyID = 2 and CreateD
    ate>='1999-09-09' and CreateDate <='2011-01-11'
        -> Order By CreateDate DESC LIMIT 140194,100
        -> ) as pp using(ID);
    +----+-------------+------------+--------+---------------+------------+---------+-------+--------+------------
    | id | select_type | table      | type   | possible_keys | key        | key_len | ref   | rows   | Extra                    |
    +----+-------------+------------+--------+---------------+------------+---------+-------+--------+------------
    |  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL       | NULL    | NULL  |    100 |                          |
    |  1 | PRIMARY     | order_list | eq_ref | PRIMARY       | PRIMARY    | 4       | pp.ID |      1 |                          |
    |  2 | DERIVED     | order_list | range  | CreateDate    | CreateDate | 8       | NULL  | 140295 | Using where; Using index |
    +----+-------------+------------+--------+---------------+------------+---------+-------+--------+------------
    3 rows in set (0.28 sec)
      

  7.   

    按照 Mablevi 的做法.效率确实不错.!