Debugging and improving sourcebans query

mysql_explain

# Before any changes

mysql> explain UPDATE `sb_submissions` SET archiv = '3', archivedby = 48 WHERE archiv = '0' AND (SteamId IN((SELECT authid FROM `sb_bans` WHERE `type` = 0 AND `RemoveType` IS NULL)) OR sip IN((SELECT ip FROM `sb_bans` WHERE `type` = 1 AND `RemoveType` IS NULL)));
+----+--------------------+----------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type        | table          | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+--------------------+----------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | UPDATE             | sb_submissions | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   758 |   100.00 | Using where |
|  3 | DEPENDENT SUBQUERY | sb_bans        | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 20239 |     1.00 | Using where |
|  2 | DEPENDENT SUBQUERY | sb_bans        | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 20239 |     1.00 | Using where |
+----+--------------------+----------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
3 rows in set (0.00 sec)

# After new index

ALTER TABLE `sb_bans` ADD INDEX `type_remove` (`type`, `RemoveType`);

mysql> explain UPDATE `sb_submissions` SET archiv = '3', archivedby = 48 WHERE archiv = '0' AND (SteamId IN((SELECT authid FROM `sb_bans` WHERE `type` = 0 AND `RemoveType` IS NULL)) OR sip IN((SELECT ip FROM `sb_bans` WHERE `type` = 1 AND `RemoveType` IS NULL)));
+----+--------------------+----------------+------------+------+---------------+-------------+---------+-------------+------+----------+------------------------------------+
| id | select_type        | table          | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra                              |
+----+--------------------+----------------+------------+------+---------------+-------------+---------+-------------+------+----------+------------------------------------+
|  1 | UPDATE             | sb_submissions | NULL       | ALL  | NULL          | NULL        | NULL    | NULL        |  758 |   100.00 | Using where                        |
|  3 | DEPENDENT SUBQUERY | sb_bans        | NULL       | ref  | type_remove   | type_remove | 13      | const,const |  183 |   100.00 | Using index condition; Using where |
|  2 | DEPENDENT SUBQUERY | sb_bans        | NULL       | ref  | type_remove   | type_remove | 13      | const,const | 7848 |   100.00 | Using index condition; Using where |
+----+--------------------+----------------+------------+------+---------------+-------------+---------+-------------+------+----------+------------------------------------+
3 rows in set (0.01 sec)

# After code changes

(query has been removed, new individual queries below)

mysql> explain SELECT authid FROM `sb_bans` WHERE `type` = 0 AND `RemoveType` IS NULL;
+----+-------------+---------+------------+------+---------------+-------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | sb_bans | NULL       | ref  | type_remove   | type_remove | 13      | const,const | 7849 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT ip FROM `sb_bans` WHERE `type` = 1 AND `RemoveType` IS NULL;
+----+-------------+---------+------------+------+---------------+-------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | sb_bans | NULL       | ref  | type_remove   | type_remove | 13      | const,const |  183 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain (select query here, with values inside the query itself... it's massive): see http://firepoweredgaming.com/sourcebanspp/something.txt

+----+-------------+----------------+------------+------+--------------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys      | key    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------------+------------+------+--------------------+--------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | sb_submissions | NULL       | ref  | archiv,sip,SteamId | archiv | 2       | const |  461 |    75.00 | Using where |
+----+-------------+----------------+------------+------+--------------------+--------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.05 sec)

There's 0 results so the update never runs.  If it did, it would run on the primary key only.

mysql_slow_queries.log

# Before any changes

# Time: 2020-01-27T02:12:31.185457Z
# User@Host: sourcebanspp[sourcebanspp] @ localhost []  Id: 17531
# Query_time: 32.825176  Lock_time: 0.000089 Rows_sent: 0  Rows_examined: 18742072
use sourcebanspp;
SET timestamp=1580091151;
UPDATE `sb_submissions` SET archiv = '3', archivedby = 48 WHERE archiv = '0' AND (SteamId IN((SELECT authid FROM `sb_bans` WHERE `type` = 0 AND `RemoveType` IS NULL)) OR sip IN((SELECT ip FROM `sb_bans` WHERE `type` = 1 AND `RemoveType` IS NULL)));

# After new index

# Time: 2020-01-27T02:19:36.195876Z
# User@Host: sourcebanspp[sourcebanspp] @ localhost []  Id: 17907
# Query_time: 9.721612  Lock_time: 0.000287 Rows_sent: 0  Rows_examined: 3677904
SET timestamp=1580091576;
UPDATE `sb_submissions` SET archiv = '3', archivedby = 48 WHERE archiv = '0' AND (SteamId IN((SELECT authid FROM `sb_bans` WHERE `type` = 0 AND `RemoveType` IS NULL)) OR sip IN((SELECT ip FROM `sb_bans` WHERE `type` = 1 AND `RemoveType` IS NULL)));

# After Code Changes

(empty)
Note: The equivelent 3-4 queries it runs returns in 0.117565 seconds total.

system-functions.php

function PruneBans()
{
    global $userbank;

    $res = $GLOBALS['db']->Execute('UPDATE `'.DB_PREFIX.'_bans` SET `RemovedBy` = 0, `RemoveType` = \'E\', `RemovedOn` = UNIX_TIMESTAMP() WHERE `length` != 0 and `ends` < UNIX_TIMESTAMP() and `RemoveType` IS NULL');
    $prot = $GLOBALS['db']->Execute("UPDATE `".DB_PREFIX."_protests` SET archiv = '3', archivedby = ".($userbank->GetAid()<0?0:$userbank->GetAid())." WHERE archiv = '0' AND bid IN((SELECT bid FROM `".DB_PREFIX."_bans` WHERE `RemoveType` = 'E'))");
    //submission = $GLOBALS['db']->Execute('UPDATE `'.DB_PREFIX.'_submissions` SET archiv = \'3\', archivedby = '.($userbank->GetAid()<0?0:$userbank->GetAid()).' WHERE archiv = \'0\' AND (SteamId IN((SELECT authid FROM `'.DB_PREFIX.'_bans` WHERE `type` = 0 AND `RemoveType` IS NULL)) OR sip IN((SELECT ip FROM `'.DB_PREFIX.'_bans` WHERE `type` = 1 AND `RemoveType` IS NULL)))');
    testImprovement($userbank);
    return $res?true:false;
}

function testImprovement($userbank) {
        $steamIDs = $GLOBALS['db']->getCol('SELECT DISTINCT authid FROM `'.DB_PREFIX.'_bans` WHERE `type` = 0 AND `RemoveType` IS NULL');
        $banIPs =  $GLOBALS['db']->getCol('SELECT DISTINCT ip FROM `'.DB_PREFIX.'_bans` WHERE `type` = 1 AND `RemoveType` IS NULL');

        $steamIDs = '"' . implode('", "', $steamIDs) . '"';
        $banIPs = '"' . implode('", "', $banIPs) . '"';

        $subIDs = $GLOBALS['db']->getCol(
            "SELECT subid
            FROM `".DB_PREFIX."_submissions`
            WHERE `archiv` = 0
            AND (
                `SteamId` IN( $steamIDs )
                OR `sip` IN( $banIPs )
            )"
        );
        if ($subIDs) {
            $subIDs = '"' . implode('", "', $subIDs) . '"';
            $submission = $GLOBALS['db']->Execute(
                'UPDATE `' . DB_PREFIX . '_submissions`
                SET archiv = 3,
                archivedby = ' . ($userbank->GetAid() < 0 ? 0 : $userbank->GetAid()) . '
                WHERE subid IN(' . $subIDs . ')'
            );
        }
      // Total execution time: 0.1175651550293 seconds.
}