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的排序顺序必须一致。