mysql多列索引使用注意
MySQL可以为多个列创建索引。一个索引可以包括15个列。
CREATE TABLE test (
id INT NOT NULL,
cola CHAR(30) NOT NULL,
colb CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (cola ,colb )
);
select * from tables where colb=’2014′;
select * from tables where cola=’c1g’ or colb=’2014′;
SELECT * from tbltables where keycola LIKE ‘%c1g%’;
select * from tables order by cola asc,colb desc;
select * from tables order by cola desc,colb asc;
以上是用不到索引的
select * from tables where cola=’c1g’
select * from tables where cola=’c1g’ and colb=’2014′;
select * from tables where cola=’c1g’ and colb>’2000′ and colb<’2015′;
select * from tables where cola=’c1g’ and (colb=’2000′ and colb=’2015′);
SELECT * from tbltables where keycola LIKE ‘c1g%’;
select * from tables order by cola asc,colb asc;
select * from tables order by cola desc,colb desc;
以上是可以用到索引的.
用于排序的column的排序顺序必须一致。
Related Posts
- mysql5.1.26rc升级至Percona mysql5.5.17 ( 2011-12-16)
- mysql InnoDB 版本一览 ( 2011-12-09)
- (小技巧)用EXCEL的CONCATENATE合并列整理数据 ( 2011-10-11)