I have a query in MYSQL like this (simplified) :
SELECT col1, SUM(DISTINCT col2) AS S
FROM tbl1
WHERE col1='abbc'
GROUP BY col1
ORDER BY S ASC
我知道在col1上建立索引对于这种查询是有用的。我想知道在(col1,col2)上建立覆盖索引是否更有用,还是没有任何区别。
我尝试了一下,它似乎不同并且更有用。
没有去重
SELECT col1, SUM(col2) AS S
FROM tbl1
WHERE col1='abbc'
GROUP BY col1
ORDER BY S ASC;
distinct
SELECT col1, SUM(distinct col2) AS S
FROM tbl1
WHERE col1='abbc'
GROUP BY col1
ORDER BY S ASC;
没有区别
SUM(DISTINCT)
。我想不出一个有效的情况。 - Gordon LinoffDISTINCT col2
,覆盖索引也必须有用。实践中需要进行实验。 - Alex Yu