mysql - 优化mysql查询: medal standings

Question

我有 2 个表:

1. 包含 gold_country、silver_country、bronze_country 列的 olympic_medalists

2. 带有国家列的标志

我想相应地列出奥运奖牌表。我有这个查询，它有效，但它似乎杀死了 mysql。希望有人可以帮助我优化查询。

SELECT DISTINCT country AS sc,
    IFNULL(
        (SELECT COUNT(silver_country) 
            FROM olympic_medalists 
        WHERE silver_country = sc AND silver_country != '' 
        GROUP BY silver_country),0) AS silver_medals, 
    IFNULL(
        (SELECT COUNT(gold_country) 
            FROM olympic_medalists 
        WHERE gold_country = sc AND gold_country != '' 
        GROUP BY gold_country),0) AS gold_medals,
    IFNULL(
        (SELECT COUNT(bronze_country) 
            FROM olympic_medalists 
        WHERE bronze_country = sc AND bronze_country != '' 
        GROUP BY bronze_country),0) AS bronze_medals
FROM olympic_medalists, flags 
GROUP BY country, gold_medals, silver_country, bronze_medals HAVING (
    silver_medals >= 1 || gold_medals >= 1 || bronze_medals >= 1)
ORDER BY gold_medals DESC, silver_medals DESC, bronze_medals DESC,   
SUM(gold_medals+silver_medals+bronze_medals)

结果会是这样的:

country  |  g  |  s  |  b  |  tot
---------------------------------
country1 |  9  |  5  |  2  |  16
country2 |  5  |  5  |  5  |  15

等等

谢谢!

olympic medalists:

  `id` int(8) NOT NULL auto_increment,
  `gold_country` varchar(64) collate utf8_unicode_ci default NULL,
  `silver_country` varchar(64) collate utf8_unicode_ci default NULL,
  `bronze_country` varchar(64) collate utf8_unicode_ci default NULL, PRIMARY KEY  (`id`)

flags

  `id` int(11) NOT NULL auto_increment,
  `country` varchar(128) default NULL,
  PRIMARY KEY  (`id`)

Answer 1

这将比您当前为交叉连接关系中的每一行执行三个不同的 SELECT 子查询的解决方案更有效(您想知道为什么它停止了!):

SELECT    a.country,
          COALESCE(b.cnt,0)     AS g,
          COALESCE(c.cnt,0)     AS s,
          COALESCE(d.cnt,0)     AS b,
          COALESCE(b.cnt,0) +
          COALESCE(c.cnt,0) +
          COALESCE(d.cnt,0)     AS tot
FROM      flags a
LEFT JOIN (
          SELECT   gold_country, COUNT(*) AS cnt 
          FROM     olympic_medalists 
          GROUP BY gold_country
          ) b ON a.country = b.gold_country
LEFT JOIN (
          SELECT   silver_country, COUNT(*) AS cnt 
          FROM     olympic_medalists 
          GROUP BY silver_country
          ) c ON a.country = c.silver_country
LEFT JOIN (
          SELECT   bronze_country, COUNT(*) AS cnt 
          FROM     olympic_medalists 
          GROUP BY bronze_country
          ) d ON a.country = d.bronze_country

---

如果不是在每个 gold、silver 和 bronze 列中存储实际的文本国家/地区名称，甚至会更快，只需存储基于整数的国家/地区 id。整数比较总是比字符串比较快。

此外，一旦您用相应的 ID 替换了 olympic_medalists 表中的每个国家/地区名称，您就需要在每一列(金牌、银牌和铜牌)上创建一个索引。

将文本名称更新为相应的 id 是一项简单的任务，可以通过单个 UPDATE 语句结合一些 ALTER 来完成TABLE 命令。