[Suggestion] Additional query optimization for #502

[coverrt]

Please test to ensure the current and proposed statements have consistent results.

---

db_selectTotalStageCount

The query plan for this statement may use temporary and filesort extras.

SurfTimer/addons/sourcemod/scripting/surftimer/sql.sp

Line 6386 in 5c4825b

Format(szQuery, 512, "SELECT SUM(c.stages) FROM (SELECT a.mapname, MAX(zonetypeid)+2 as stages FROM `ck_zones` a RIGHT JOIN `ck_maptier` b ON a.mapname = b.mapname WHERE zonetype = 3 GROUP BY a.mapname)c");

Recommend statement

RIGHT JOIN changed to INNER JOIN to ensure the mapname column has matching values in both ck_zones and ck_maptier.

SELECT
    COUNT(DISTINCT `a`.`mapname`) + COUNT(1) AS `count`
FROM
    `ck_zones` `a`
INNER JOIN
    `ck_maptier` `b` ON
        `b`.`mapname` = `a`.`mapname`
WHERE
    `a`.`zonetype` = 3
    AND
    `a`.`zonegroup` = 0;

Current statement query plan

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY		ALL	\N	\N	\N	\N	1270
2	DERIVED	b	index	PRIMARY	PRIMARY	218	\N	254	Using index; Using temporary; Using filesort
2	DERIVED	a	ref	PRIMARY	PRIMARY	218	surftimer.b.mapname	5	Using where

Recommended statement query plan

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	b	index	PRIMARY	PRIMARY	218	\N	254	Using index
1	SIMPLE	a	ref	PRIMARY	PRIMARY	218	surftimer.b.mapname	5	Using where

db_selectTotalBonusCount

SurfTimer/addons/sourcemod/scripting/surftimer/sql.sp

Line 6359 in 5c4825b

Format(szQuery, 512, "SELECT COUNT(DISTINCT a.mapname,zonegroup) as count FROM ck_zones a RIGHT JOIN ck_maptier b ON a.mapname = b.mapname WHERE a.zonegroup > 0");

Recommended statement

Added additional WHERE clause for zonetype returning only start bonus zones, reducing records processed for aggregation.

RIGHT JOIN changed to INNER JOIN to ensure the mapname column has matching values in both ck_zones and ck_maptier.

SELECT
    COUNT(DISTINCT `a`.`mapname`, `a`.`zonegroup`) AS `count`
FROM
    `ck_zones` `a`
INNER JOIN
    `ck_maptier` `b` ON
        `b`.`mapname` = `a`.`mapname`
WHERE
    `a`.`zonetype` = 1
    AND
    `a`.`zonegroup` > 0;

Current statement query plan

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	b	index	PRIMARY	PRIMARY	218	\N	254	Using index
1	SIMPLE	a	ref	PRIMARY	PRIMARY	218	surftimer.b.mapname	5	Using where

Recommended statement query plan

The query plan for this statement is not altered.

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	b	index	PRIMARY	PRIMARY	218	\N	254	Using index
1	SIMPLE	a	ref	PRIMARY	PRIMARY	218	surftimer.b.mapname	5	Using where

[RSCSurf]

Yeah, it would be better to use JOIN instead of RIGHT JOIN