Tyler Ziegler
Reputation: 11
Properly optimizing MySQL query
Folks, I'm having some trouble with this query here. It's for a counterstrike go server. This query is taking up to 20 seconds to process depending on the map. Maps may be linear, meaning that they are one long stage with multiple checkpoints, or staged with one checkpoint for each stage in the map. Some maps may also have a bonus. We are finding that maps with multiple bonus stages are somehow causing the query to run for an extended period of time.
SELECT zonegroup, cp1, cp2, cp3, cp4, cp5, cp6, cp7, cp8, cp9, cp10, cp11, cp12, cp13, cp14, cp15, cp16, cp17, cp18, cp19, cp20, cp21, cp22, cp23, cp24, cp25, cp26, cp27, cp28, cp29, cp30, cp31, cp32, cp33, cp34, cp35
FROM ck_checkpoints
WHERE steamid = '%s' AND mapname='%s'
UNION
SELECT a.zonegroup, b.cp1, b.cp2, b.cp3, b.cp4, b.cp5, b.cp6, b.cp7, b.cp8, b.cp9, b.cp10, b.cp11, b.cp12, b.cp13, b.cp14, b.cp15, b.cp16, b.cp17, b.cp18, b.cp19, b.cp20, b.cp21, b.cp22, b.cp23, b.cp24, b.cp25, b.cp26, b.cp27, b.cp28, b.cp29, b.cp30, b.cp31, b.cp32, b.cp33, b.cp34, b.cp35
FROM ck_bonus a
LEFT JOIN ck_checkpoints b
ON a.steamid = b.steamid AND a.zonegroup = b.zonegroup
WHERE a.mapname = '%s'
GROUP BY a.zonegroup";
The structure of the two tables in question: https://pastebin.com/x5px68Q4
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `surf`
--
-- --------------------------------------------------------
--
-- Table structure for table `ck_bonus`
--
CREATE TABLE IF NOT EXISTS `ck_bonus` (
`steamid` varchar(32) NOT NULL DEFAULT '',
`name` varchar(32) DEFAULT NULL,
`mapname` varchar(32) NOT NULL DEFAULT '',
`runtime` float NOT NULL DEFAULT '-1',
`zonegroup` int(12) NOT NULL DEFAULT '1',
`startspeed` float NOT NULL DEFAULT '-1'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Table structure for table `ck_checkpoints`
--
CREATE TABLE IF NOT EXISTS `ck_checkpoints` (
`steamid` varchar(32) NOT NULL DEFAULT '',
`mapname` varchar(32) NOT NULL DEFAULT '',
`cp1` float DEFAULT '0',
`cp2` float DEFAULT '0',
`cp3` float DEFAULT '0',
`cp4` float DEFAULT '0',
`cp5` float DEFAULT '0',
`cp6` float DEFAULT '0',
`cp7` float DEFAULT '0',
`cp8` float DEFAULT '0',
`cp9` float DEFAULT '0',
`cp10` float DEFAULT '0',
`cp11` float DEFAULT '0',
`cp12` float DEFAULT '0',
`cp13` float DEFAULT '0',
`cp14` float DEFAULT '0',
`cp15` float DEFAULT '0',
`cp16` float DEFAULT '0',
`cp17` float DEFAULT '0',
`cp18` float DEFAULT '0',
`cp19` float DEFAULT '0',
`cp20` float DEFAULT '0',
`cp21` float DEFAULT '0',
`cp22` float DEFAULT '0',
`cp23` float DEFAULT '0',
`cp24` float DEFAULT '0',
`cp25` float DEFAULT '0',
`cp26` float DEFAULT '0',
`cp27` float DEFAULT '0',
`cp28` float DEFAULT '0',
`cp29` float DEFAULT '0',
`cp30` float DEFAULT '0',
`cp31` float DEFAULT '0',
`cp32` float DEFAULT '0',
`cp33` float DEFAULT '0',
`cp34` float DEFAULT '0',
`cp35` float DEFAULT '0',
`zonegroup` int(12) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `ck_bonus`
--
ALTER TABLE `ck_bonus`
ADD PRIMARY KEY (`steamid`,`mapname`,`zonegroup`),
ADD KEY `bonusrank` (`mapname`,`runtime`,`zonegroup`);
--
-- Indexes for table `ck_checkpoints`
--
ALTER TABLE `ck_checkpoints`
ADD PRIMARY KEY (`steamid`,`mapname`,`zonegroup`);
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
An output of profile/explain on the query: https://pastebin.com/PRjCZUwN
mysql> SET PROFILING=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT zonegroup, cp1, cp2, cp3, cp4, cp5, cp6, cp7, cp8, cp9, cp10, cp11, cp12, cp13, cp14, cp15, cp16, cp17, cp18, cp19, cp20, cp21, cp22, cp23, cp24, cp25, cp26, cp27, cp28, cp29, cp30, cp31, cp32, cp33, cp34, cp35 FROM ck_checkpoints
WHERE steamid = 'STEAM_1:0:4040087'
AND mapname='surf_mom'
UNION
SELECT a.zonegroup, b.cp1, b.cp2, b.cp3, b.cp4, b.cp5, b.cp6, b.cp7, b.cp8, b.cp9, b.cp10, b.cp11, b.cp12, b.cp13, b.cp14, b.cp15, b.cp16, b.cp17, b.cp18, b.cp19, b.cp20, b.cp21, b.cp22, b.cp23, b.cp24, b.cp25, b.cp26, b.cp27, b.cp28, b.cp29, b.cp30, b.cp31, b.cp32, b.cp33, b.cp34, b.cp35
FROM ck_bonus a
LEFT JOIN ck_checkpoints b
ON a.steamid = b.steamid
AND a.zonegroup = b.zonegroup
WHERE a.mapname = 'surf_mom'
GROUP BY a.zonegroup;
+-----------+---------+---------+---------+--------+---------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| zonegroup | cp1 | cp2 | cp3 | cp4 | cp5 | cp6 | cp7 | cp8 | cp9 | cp10 | cp11 | cp12 | cp13 | cp14 | cp15 | cp16 | cp17 | cp18 | cp19 | cp20 | cp21 | cp22 | cp23 | cp24 | cp25 | cp26 | cp27 | cp28 | cp29 | cp30 | cp31 | cp32 | cp33 | cp34 | cp35 |
+-----------+---------+---------+---------+--------+---------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| 0 | 5.92969 | 12.2344 | 20.1328 | 27.832 | 34.7344 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+-----------+---------+---------+---------+--------+---------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
2 rows in set (27.47 sec)
mysql> SHOW PROFILE FOR QUERY 0;
+----------------------+-----------+
| Status | Duration |
+----------------------+-----------+
| starting | 0.000243 |
| checking permissions | 0.000017 |
| checking permissions | 0.000011 |
| checking permissions | 0.000017 |
| Opening tables | 0.000188 |
| System lock | 0.000026 |
| optimizing | 0.000058 |
| statistics | 0.000102 |
| preparing | 0.000031 |
| optimizing | 0.000019 |
| statistics | 0.000076 |
| preparing | 0.002882 |
| Creating tmp table | 0.000118 |
| Sorting result | 0.000020 |
| executing | 0.000012 |
| Sending data | 0.000195 |
| executing | 0.000014 |
| Sending data | 27.463481 |
| Creating sort index | 0.000126 |
| optimizing | 0.000015 |
| statistics | 0.000023 |
| preparing | 0.000024 |
| executing | 0.000013 |
| Sending data | 0.000043 |
| removing tmp table | 0.000017 |
| Sending data | 0.000014 |
| removing tmp table | 0.000013 |
| Sending data | 0.000012 |
| query end | 0.000016 |
| closing tables | 0.000026 |
| freeing items | 0.000323 |
| logging slow query | 0.000065 |
| cleaning up | 0.000030 |
+----------------------+-----------+
33 rows in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT zonegroup, cp1, cp2, cp3, cp4, cp5, cp6, cp7, cp8, cp9, cp10, cp11, cp12, cp13, cp14, cp15, cp16, cp17, cp18, cp19, cp20, cp21, cp22, cp23, cp24, cp25, cp26, cp27, cp28, cp29, cp30, cp31, cp32, cp33, cp34, cp35
FROM ck_checkpoints
WHERE steamid = 'STEAM_1:0:4040087'
AND mapname='surf_mom'
UNION
SELECT a.zonegroup, b.cp1, b.cp2, b.cp3, b.cp4, b.cp5, b.cp6, b.cp7, b.cp8, b.cp9, b.cp10, b.cp11, b.cp12, b.cp13, b.cp14, b.cp15, b.cp16, b.cp17, b.cp18, b.cp19, b.cp20, b.cp21, b.cp22, b.cp23, b.cp24, b.cp25, b.cp26, b.cp27, b.cp28, b.cp29, b.cp30, b.cp31, b.cp32, b.cp33, b.cp34, b.cp35
FROM ck_bonus a
LEFT JOIN ck_checkpoints b
ON a.steamid = b.steamid
AND a.zonegroup = b.zonegroup
WHERE a.mapname = 'surf_mom'
GROUP BY a.zonegroup;
+----+--------------+----------------+------+-------------------+-----------+---------+-------------+--------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+----------------+------+-------------------+-----------+---------+-------------+--------+--------------------------------------------------------+
| 1 | PRIMARY | ck_checkpoints | ref | PRIMARY | PRIMARY | 68 | const,const | 2 | Using where |
| 2 | UNION | a | ref | PRIMARY,bonusrank | bonusrank | 98 | const | 1062 | Using index condition; Using temporary; Using filesort |
| 2 | UNION | b | ALL | NULL | NULL | NULL | NULL | 159232 | Using where; Using join buffer (Block Nested Loop) |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+----------------+------+-------------------+-----------+---------+-------------+--------+--------------------------------------------------------+
4 rows in set (0.00 sec)
mysql> SHOW INDEXES FROM ck_checkpoints;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ck_checkpoints | 0 | PRIMARY | 1 | steamid | A | 53077 | NULL | NULL | | BTREE | | |
| ck_checkpoints | 0 | PRIMARY | 2 | mapname | A | 159233 | NULL | NULL | | BTREE | | |
| ck_checkpoints | 0 | PRIMARY | 3 | zonegroup | A | 159233 | NULL | NULL | | BTREE | | |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> SHOW INDEXES FROM ck_bonus;
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ck_bonus | 0 | PRIMARY | 1 | steamid | A | 6236 | NULL | NULL | | BTREE | | |
| ck_bonus | 0 | PRIMARY | 2 | mapname | A | 24945 | NULL | NULL | | BTREE | | |
| ck_bonus | 0 | PRIMARY | 3 | zonegroup | A | 24945 | NULL | NULL | | BTREE | | |
| ck_bonus | 1 | bonusrank | 1 | mapname | A | 218 | NULL | NULL | | BTREE | | |
| ck_bonus | 1 | bonusrank | 2 | runtime | A | 24945 | NULL | NULL | | BTREE | | |
| ck_bonus | 1 | bonusrank | 3 | zonegroup | A | 24945 | NULL | NULL | | BTREE | | |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)
Code from the plugin:
char sql_selectRecordCheckpoints[] = "SELECT zonegroup, cp1, cp2, cp3, cp4, cp5, cp6, cp7, cp8, cp9, cp10, cp11, cp12, cp13, cp14, cp15, cp16, cp17, cp18, cp19, cp20, cp21, cp22, cp23, cp24, cp25, cp26, cp27, cp28, cp29, cp30, cp31, cp32, cp33, cp34, cp35 FROM ck_checkpoints WHERE steamid = '%s' AND mapname='%s' UNION SELECT a.zonegroup, b.cp1, b.cp2, b.cp3, b.cp4, b.cp5, b.cp6, b.cp7, b.cp8, b.cp9, b.cp10, b.cp11, b.cp12, b.cp13, b.cp14, b.cp15, b.cp16, b.cp17, b.cp18, b.cp19, b.cp20, b.cp21, b.cp22, b.cp23, b.cp24, b.cp25, b.cp26, b.cp27, b.cp28, b.cp29, b.cp30, b.cp31, b.cp32, b.cp33, b.cp34, b.cp35 FROM ck_bonus a LEFT JOIN ck_checkpoints b ON a.steamid = b.steamid AND a.zonegroup = b.zonegroup WHERE a.mapname = '%s' GROUP BY a.zonegroup";
public void db_viewRecordCheckpointInMap()
{
for (int k = 0; k < MAXZONEGROUPS; k++)
{
g_bCheckpointRecordFound[k] = false;
for (int i = 0; i < CPLIMIT; i++)
g_fCheckpointServerRecord[k][i] = 0.0;
}
char szQuery[1028];
Format(szQuery, 1028, sql_selectRecordCheckpoints, g_szRecordMapSteamID, g_szMapName, g_szMapName);
SQL_TQuery(g_hDb, sql_selectRecordCheckpointsCallback, szQuery, 1, DBPrio_Low);
}
public void sql_selectRecordCheckpointsCallback(Handle owner, Handle hndl, const char[] error, any data)
{
if (hndl == null)
{
LogError("[Surf Timer] SQL Error (sql_selectRecordCheckpointsCallback): %s", error);
if (!g_bServerDataLoaded)
db_CalcAvgRunTime();
return;
}
if (SQL_HasResultSet(hndl))
{
int zonegroup;
while (SQL_FetchRow(hndl))
{
zonegroup = SQL_FetchInt(hndl, 0);
for (int i = 0; i < CPLIMIT; i++)
{
g_fCheckpointServerRecord[zonegroup][i] = SQL_FetchFloat(hndl, (i + 1));
if (!g_bCheckpointRecordFound[zonegroup] && g_fCheckpointServerRecord[zonegroup][i] > 0.0)
g_bCheckpointRecordFound[zonegroup] = true;
}
}
}
if (!g_bServerDataLoaded)
db_CalcAvgRunTime();
return;
}
I'm a novice so any help is much appreciated!
Upvotes: 1
Views: 69
Answers (2)
Rick James
Reputation: 142298
For ck_checkpoints:
The PK takes care of the first SELECT;
INDEX(zonegroup, steamid) -- for JOIN (order not important)
For ck_bonus:
INDEX(mapname, zonegroup) -- for second SELECT (order is important)
For ck_bonus, consider (instead) changing the PK to (mapname, zonegroup, steamid). But be cautious; this could mess up other queries.
There is a potential problem with the LEFT JOIN + GROUP BY: Which b values do you want for a given a.zonegroup? Or is the mapping 1:1, but could be missing from b? In the latter case, you want a row of NULLs? If never getting NULLs, then use plain JOIN.
As Michael points out, be consistent on CHARACTER SET, else the indexes may be ignored -- a big performance loss.
Don't use MyISAM! Switch to InnoDB. Some of what I have said works better for InnoDB. Conversion tips
How big are these tables? How much RAM do you have? Is CHAR(32) some form of UUID? The answers to these may lead to more performance tips.
Upvotes: 0
Michael
Reputation: 548
The basic problem is that MySQL isn't using an index for the join of ck_bonus to ck_checkpoints, so it's doing a full table scan, which is taking forever. But there are two reasons for that:
- There isn't an index it can fully take advantage of for this join. Create composite indexes on (ck_bonus.steamid, ck_bonus.zonegroup) and (ck_checkpoints.steamid, ck_checkpoints.zonegroup).
- The tables are using two different character sets and collations, so MySQL can't compare the indexes between the tables at all, anyway. Either convert one of the tables to match the other, or convert those columns as part of the query.
These changes will make the query much, much faster.
Upvotes: 1