mysql - 索引 btree 不起作用

Question

我有一张 OrderDish 表，其中包含:

create table OrderDish(
    email VARCHAR(80),
    nOrd integer,
    totalPrice integer,
    sale integer,
    nameR VARCHAR(80),
    dayO integer,
    monthO VARCHAR(45),
    yearO integer,
    constraint c5 primary key (email, nOrd),
    foreign key (email) references Client(email),
    foreign key (nameR) references Restaurant(nameR),
    foreign key (dayO, monthO, yearO) references Data(dayO, monthO, yearO));

我有一个非常常见的查询，它询问餐厅和年份销售的 totalPrice。

select O.nameR, O.yearO, sum(O.totalPrice) as mytotal
from OrderDish O
group by O.nameR, O.yearO;

结果是:

+------------------------------+------+------------+
| nameR                        | yeaO | mytotal |
+------------------------------+------+------------+
| El Gordo                     | 2013 |       5699 |
| El Gordo                     | 2014 |       2793 |
| El Gordo                     | 2015 |      11457 |
| Eleven                       | 2013 |       7599 |
| Eleven                       | 2014 |       3724 |
........

我想创建一些索引来加速这个非常常见的查询。到目前为止我已经这样做了

create INDEX tot_per_rest_per_year 
    ON OrderDish(nameR) using btree;

但这并没有加速——消耗的时间是一样的。

Answer 1

我认为遵循 INDEX 可以帮助您。

ALTER TABLE OrderDish ADD INDEX(nameR, yearO, totalPrice);

你是全盘扫描，即使有INDEX(nameR)。

引用http://dev.mysql.com/doc/refman/5.5/en/group-by-optimization.html

Assume that there is an index idx(c1,c2,c3) on table t1(c1,c2,c3,c4). The loose index scan access method can be used for the following queries:

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

和，

" i only know btree in mysql "

MySQL只支持B-TREE INDEX(除了MEMORY Table只支持HASH INDEX)