mysql - Brighthouse group_concat的替代方案？

我正在使用一个大型数据库，其中有一些关键查询依赖于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和一个brightwouse数据库，里面有相同的数据。如果我只是做简单的选择显然brighthouse有巨大的性能优势，所以我希望找到一种方法来运行一个具有相同结果的查询，可以利用brighthouse的速度。
编辑：为了纠正我自己的错误，我想找到一个查询，用Brighthouse引擎实现同样的功能，而不是像我之前所说的innodb。

最佳答案

我最终发现我可以重新安排一些事情来得到我想要的东西。我的原始问题中的查询可以重写为：

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

…它允许相同的结果集并利用了Brighthouse引擎！:-)