我使用的是一个大型数据库,其中的一些关键查询依赖于group_concat (通过myisam)在分组时正确地对多个列进行排序。从本质上讲,这样我们就可以确保在每一行中获得正确的数据。例如,如果我想知道在每个邮政编码的特定州有多少条记录,但每个地址只有一条记录:
(id是唯一的无符号数字)
SELECT state AS state,
zip AS zip,
COUNT(id) AS total
FROM (SELECT CAST(Substring_index(GROUP_CONCAT(id ORDER BY state, zip, city,
address,
id),
',', 1)
AS UNSIGNED) AS id,
CAST(Substring_index(GROUP_CONCAT(zip ORDER BY state, zip, city,
address
, id),
',', 1
) AS UNSIGNED) AS zip,
CAST(Substring_index(GROUP_CONCAT(state ORDER BY state, zip, city
,
address, id),
',',
1) AS CHAR) AS state
FROM DATABASE.table
GROUP BY st,
zip,
city,
address) AS a
GROUP BY zip
ORDER BY zip 我有一个myisam和一个有相同数据的brighthouse数据库。如果我只是做简单的选择,显然brighthouse具有巨大的性能优势,所以我希望找到一种方法来运行具有相同结果的查询,从而利用brighthouse的速度。
编辑:为了纠正我自己,我想找到一个查询,它可以用Brighthouse引擎完成同样的事情,而不是像我之前说的那样是innodb。
发布于 2011-07-29 05:17:07
我最终发现我可以重新安排一些东西来得到我想要的东西。我的原始问题中的查询可以重写为:
SELECT state AS state,
zip AS zip,
COUNT(id) AS total
FROM (SELECT state,
zip,
id
FROM (SELECT state,
zip,
id,
address
FROM DATABASE.table
ORDER BY id) AS a
GROUP BY address) AS a2
GROUP BY zip
ORDER BY zip ...which允许使用相同的结果集,并利用了Brighthouse引擎!:-)
https://stackoverflow.com/questions/6765494
复制相似问题