CREATE TABLE 'key_t'
(
'id' int(11) NOT NULL auto_increment,
'key1' int(11) NOT NULL default '0',
'key2' int(11) NOT NULL default '0',
'key3' int(11) NOT NULL default '0',
PRIMARY KEY ('id'),
KEY 'normal_key' ('key1','key2','key3')
)ENGINE=InnoDB 这两条个SQL在上面的表上执行,Where子句和Order by 子句是否都用到了normal_key索引? 为什么?
select * from key_t where key1=1 order by key3
select count(id) from key_t where key1=777group by key3(上面的语句是针对MySQL的,但道理应该和MS SQL Server一样)
(
'id' int(11) NOT NULL auto_increment,
'key1' int(11) NOT NULL default '0',
'key2' int(11) NOT NULL default '0',
'key3' int(11) NOT NULL default '0',
PRIMARY KEY ('id'),
KEY 'normal_key' ('key1','key2','key3')
)ENGINE=InnoDB 这两条个SQL在上面的表上执行,Where子句和Order by 子句是否都用到了normal_key索引? 为什么?
select * from key_t where key1=1 order by key3
select count(id) from key_t where key1=777group by key3(上面的语句是针对MySQL的,但道理应该和MS SQL Server一样)
+----+-------------+-------+------+---------------+------------+---------+-------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-------+------+------------------------------------------+
| 1 | SIMPLE | key_t | ref | normal_key | normal_key | 4 | const | 1 | Using where; Using index; Using filesort |
+----+-------------+-------+------+---------------+------------+---------+-------+------+------------------------------------------+
1 row in set (0.03 sec)