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.就无法使用索引.难道索引在这上面也是有限制的吗?
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.就无法使用索引.难道索引在这上面也是有限制的吗?
解决方案 »
- 求助#1064 - You have an error in your SQL syntax; check the manual that correspond
- 关于MySQL中表列的限制
- 有关索引的疑问
- 奇怪,我的mysql采用任何的密码登录root都对
- 在不影响前台用户使用的情况下,把远程数据库导入本地
- 帮帮忙吧!寻找Microsoft SQL Server 7数据库技术指南
- 急!急1急!asp中的mysql连接
- MYSQL里如何设置密码?
- Mysql 表名可以使用常量或者嵌套么?
- mysql update 如何强制不加锁
- 联合索引的问题
- mysql运用_RecordsetPtr时新增数据报错
贴结果
+------------+------------+------------+--------------+-------------------+-----
------+-------------+----------+--------+------+------------+---------+
| 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>
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>
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 看看什么结果
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)