比如现在有表:
PRODUCT(name, manufacturer, price, description, quality, mdate)其中(name, manufacturer) is a primary key那么:
SELECT *
FROM PRODUCT
WHERE name = 'computer'and manufacturer = 'IBM';
这个会按index查询吧,那如果我把and两边的位置调换一下呢:
SELECT *
FROM PRODUCT
WHERE manufacturer = 'IBM' AND name = 'computer';
这样还会按index查询吗?
PRODUCT(name, manufacturer, price, description, quality, mdate)其中(name, manufacturer) is a primary key那么:
SELECT *
FROM PRODUCT
WHERE name = 'computer'and manufacturer = 'IBM';
这个会按index查询吧,那如果我把and两边的位置调换一下呢:
SELECT *
FROM PRODUCT
WHERE manufacturer = 'IBM' AND name = 'computer';
这样还会按index查询吗?
WHERE name = 'computer'and manufacturer = 'IBM';这个会按index查询.WHERE manufacturer = 'IBM' AND name = 'computer' 这个不会按index查询.
WHERE name = 'computer';
或者
WHERE manufacturer = 'IBM';
当楼主的索引是name + manufacturer时:
where name = '' and manufacturer = '' 走索引
where manufacturer = '' and manufacturer = '' 走索引
where name = '' 走索引
where manufacturer = '' 不走索引当使用复合索引的前几列进行查询时(必须是从第1列开始),仍然是走索引的。
2、从基于rule的执行计划考虑
你建立的索引是符合索引 (name, manufacturer) is a primary key
name这个列是引导列,只要你的where条件中用到了这个列,那么就会走索引。 同样的道理 (col1,col2,col3) 这也叫符合索引
只要where 条件用到col1 那么这个索引就会走。
如:where col1=xxx and col3=xxx 或者
where col1=xxx and col2=xxx 或者
where col1=xxx and col2=xxx and col3=xxx