I'm having an issue with a particular SQL query running on one of my databases:

select
    `map`,
    `tier`,
     (select count(*) from mapzones a where a.map = b.map
           and `track` = 0 and `type` > 0) as `stages`,
     (select count(*) from mapzones a where a.map = b.map
           and `track` > 0 and `type` > 0) as `bonuses`
from
    maptiers b
order by `map` asc;

The response takes ~28-30 seconds to execute, which is much too slow for what I need.

Strangely, when I execute a similar query on another database for a separate gamemode, it takes <100ms, usually <50ms:

select
    `mapname`,
    `tier`,
     (select count(*) from ck_zones a where a.mapname = b.mapname
       and `zonegroup` = 0 and (zonetype = 2
                             or zonetype = 3)) as `stages`,
     (select count(*) from ck_zones a where a.mapname = b.mapname 
       and `zonegroup` > 0 and `zonetype` = 0) as `bonuses`
from
    ck_maptier b
order by `mapname` asc;

Server Info

• Server version: 8.0.29 - MySQL Community Server - GPL

Collation/Type

First Query's Database (BhopTimer)

Second Query's Database (SurfTimer)

(from Comment)

SELECT  a.map, a.tier, b.stage_count
    FROM  maptiers a
    INNER JOIN  
    (
        SELECT  DISTINCT map,
                COUNT(CASE WHEN (type > 0 AND  track = 0) THEN 1 END)
                         OVER (PARTITION BY map) AS `stage_count`
            FROM  mapzones
    ) AS b  ON a.map = b.map
    ORDER BY  a.map;

Create Tables

CREATE TABLE `maptiers` (
  `map` varchar(255) NOT NULL,
  `tier` int NOT NULL DEFAULT '1',
  PRIMARY KEY (`map`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `mapzones` (
  `id` int NOT NULL AUTO_INCREMENT,
  `map` varchar(255) NOT NULL,
  `type` int DEFAULT NULL,
  `corner1_x` float DEFAULT NULL,
  `corner1_y` float DEFAULT NULL,
  `corner1_z` float DEFAULT NULL,
  `corner2_x` float DEFAULT NULL,
  `corner2_y` float DEFAULT NULL,
  `corner2_z` float DEFAULT NULL,
  `destination_x` float NOT NULL DEFAULT '0',
  `destination_y` float NOT NULL DEFAULT '0',
  `destination_z` float NOT NULL DEFAULT '0',
  `track` int NOT NULL DEFAULT '0',
  `flags` int DEFAULT '0',
  `data` int DEFAULT '0',
  `form` tinyint DEFAULT NULL,
  `target` varchar(63) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14612 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `ck_maptier` (
  `mapname` varchar(54) NOT NULL,
  `tier` int NOT NULL,
  `maxvelocity` float NOT NULL DEFAULT '3500',
  `announcerecord` int NOT NULL DEFAULT '0',
  `gravityfix` int NOT NULL DEFAULT '1',
  `ranked` int NOT NULL DEFAULT '1',
  `stages` int DEFAULT NULL,
  `bonuses` int DEFAULT NULL,
  PRIMARY KEY (`mapname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `ck_zones` (
  `mapname` varchar(54) NOT NULL,
  `zoneid` int NOT NULL DEFAULT '-1',
  `zonetype` int DEFAULT '-1',
  `zonetypeid` int DEFAULT '-1',
  `pointa_x` float DEFAULT '-1',
  `pointa_y` float DEFAULT '-1',
  `pointa_z` float DEFAULT '-1',
  `pointb_x` float DEFAULT '-1',
  `pointb_y` float DEFAULT '-1',
  `pointb_z` float DEFAULT '-1',
  `vis` int DEFAULT '0',
  `team` int DEFAULT '0',
  `zonegroup` int NOT NULL DEFAULT '0',
  `zonename` varchar(128) DEFAULT NULL,
  `hookname` varchar(128) DEFAULT 'None',
  `targetname` varchar(128) DEFAULT 'player',
  `onejumplimit` int NOT NULL DEFAULT '1',
  `prespeed` int NOT NULL DEFAULT '350',
  PRIMARY KEY (`mapname`,`zoneid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Samples

First Query (BhopTimer)

maptiers.sql mapzones.sql

Second Query (SurfTimer)

ck_maptier.sql ck_zones.sql

CodePudding user response：

See if they run faster when turned inside out:

SELECT  b.map,
        b.tier,
        x.stages,
        x.bonuses
    FROM ( SELECT map,
                 SUM(track = 0 AND type = 0) AS stages,
                 SUM(track > 0 AND type > 0) AS bonuses
               FROM mapzones
               GROUP BY map ) AS x
    JOIN maptiers AS b  ON b.map = x.map
    ORDER BY map ASC;

INDEXes:

mapzones:  INDEX(map, track, type)
maptiers:  INDEX(map, tier)
    

(For further discussion, please provide SHOW CREATE TABLE for each table. Also: EXPLAIN SELECT ...)