Resolving Slow Player Max Trophies Update Due to Missing Indexes & Subquery Optimization

Understanding the Performance Bottleneck in Updating Player Max Trophies from Battle Records

Issue Overview: Correlated Subqueries with Full Table Scans on Large Battles Data

The goal is to update the max_trophies field in the players table with the highest trophy value each player has ever achieved in battles, whether as a winner or loser. The original approach uses two UPDATE statements with correlated subqueries against the battles table: one for winner_trophies and another for loser_trophies. Each subquery attempts to find the maximum trophy value for a player’s participation in battles.

The core problem lies in the execution plan of these subqueries. Without proper indexing, SQLite must perform full table scans of the battles table for every row in the players table. If there are N players and M battles, the worst-case complexity is O(N*M) for each UPDATE statement. With two such statements, the total cost becomes O(2*N*M), which is unsustainable for large datasets.

The players table’s max_trophies field is stored as TEXT, while the winner_trophies and loser_trophies fields in battles are INTEGER. This type mismatch forces implicit conversions during comparisons (e.g., winner_trophies > max_trophies), adding computational overhead.

The existing unique constraint on battles(battle_time, winner_tag, loser_tag) does not assist with filtering by winner_tag or loser_tag in subqueries because the index is ordered by battle_time first. SQLite cannot efficiently locate rows where winner_tag or loser_tag matches a specific player_tag without scanning the entire index or table.

Root Causes: Missing Indexes, Subquery Design, and Data Type Mismatch

1. Absence of Filtering Indexes on winner_tag and loser_tag
   SQLite relies on indexes to optimize WHERE clauses and sorting operations. The existing unique index on battles(battle_time, winner_tag, loser_tag) is ineffective for queries filtering by winner_tag or loser_tag because the first column in the index is battle_time. Indexes are most efficient when the leading column matches the filter criteria.

2. Correlated Subqueries with Repeated Full Scans
   Correlated subqueries (subqueries that reference columns from the outer players table) execute once per row in the outer query. Without an index to satisfy the WHERE winner_tag = players.player_tag or WHERE loser_tag = players.player_tag conditions, each subquery requires a full scan of the battles table.

3. Data Type Inconsistency in Trophy Comparisons
   Comparing INTEGER trophy values (winner_trophies, loser_trophies) with the TEXT-typed max_trophies forces SQLite to perform type conversions. This slows down comparisons and risks incorrect results if non-numeric values exist in max_trophies.

4. Redundant GROUP BY and ORDER BY Clauses
   The second attempt uses SELECT MAX(winner_trophies) ... GROUP BY winner_tag. The GROUP BY is redundant here because the subquery is already correlated to a single player_tag. Similarly, ORDER BY ... LIMIT 1 in the initial approach is less efficient than using MAX().

5. Lack of Composite Indexes Covering Trophy Values
   Even if an index on winner_tag or loser_tag existed, the absence of winner_trophies or loser_trophies in the index would require SQLite to access the table data (a "rowid lookup") to retrieve trophy values, adding I/O overhead.

Optimization Strategy: Index Creation, Query Rewriting, and Schema Adjustments

Step 1: Create Composite Indexes for Winner and Loser Tags with Trophies

Create two non-unique composite indexes to accelerate lookups by winner_tag and loser_tag, while including the trophy values to avoid table accesses:

CREATE INDEX idx_battles_winner_tag ON battles(winner_tag, winner_trophies);
CREATE INDEX idx_battles_loser_tag ON battles(loser_tag, loser_trophies);

These indexes allow SQLite to:

• Quickly find all battles involving a specific player_tag (as winner or loser).
• Retrieve the maximum trophy value directly from the index (via MAX() or ORDER BY ... LIMIT 1).

Step 2: Rewrite the UPDATE Queries to Use MAX() and Combine Conditions

Replace the two separate UPDATE statements with a single statement that considers both winner and loser trophies. Use a COALESCE function to handle cases where a player has only won or only lost battles:

UPDATE players
SET max_trophies = (
  SELECT MAX(trophies)
  FROM (
    SELECT winner_trophies AS trophies
    FROM battles
    WHERE winner_tag = players.player_tag
    UNION ALL
    SELECT loser_trophies AS trophies
    FROM battles
    WHERE loser_tag = players.player_tag
  )
);

The UNION ALL combines all trophy values from both roles, and MAX() selects the highest.

Step 3: Add a Conditional Update to Skip Unnecessary Writes

To avoid updating rows where the new max_trophies isn’t greater than the existing value, add a WHERE clause:

UPDATE players
SET max_trophies = new_max.new_trophies
FROM (
  SELECT 
    players.player_tag,
    MAX(
      COALESCE(
        (SELECT MAX(winner_trophies) FROM battles WHERE winner_tag = players.player_tag),
        (SELECT MAX(loser_trophies) FROM battles WHERE loser_tag = players.player_tag),
        0
      )
    ) AS new_trophies
  FROM players
) AS new_max
WHERE players.player_tag = new_max.player_tag
  AND CAST(players.max_trophies AS INTEGER) < new_max.new_trophies;

This uses a derived table (new_max) to compute the maximum trophy value for each player and compares it against the current max_trophies after converting it to an integer.

Step 4: Correct the Data Type of max_trophies

Alter the players table to store max_trophies as INTEGER:

CREATE TABLE players_new(
    player_tag TEXT,
    update_date TEXT,
    max_trophies INTEGER,  -- Changed from TEXT
    UNIQUE(player_tag)
);
INSERT INTO players_new SELECT * FROM players;
DROP TABLE players;
ALTER TABLE players_new RENAME TO players;

This eliminates implicit type conversions and ensures correct comparisons.

Step 5: Analyze the Query Plan with EXPLAIN QUERY PLAN

Use SQLite’s EXPLAIN QUERY PLAN to verify that the indexes are being used:

EXPLAIN QUERY PLAN
SELECT MAX(winner_trophies)
FROM battles
WHERE winner_tag = 'PLAYER_TAG_EXAMPLE';

The output should include SEARCH battles USING INDEX idx_battles_winner_tag (winner_tag=?), confirming index usage.

Step 6: Batch Processing for Very Large Datasets

If the players table is extremely large (millions of rows), process updates in batches to reduce transaction overhead and memory usage:

BEGIN TRANSACTION;
UPDATE players
SET max_trophies = ...
WHERE player_tag IN (SELECT player_tag FROM players LIMIT 1000 OFFSET 0);
COMMIT;
-- Repeat with OFFSET 1000, 2000, etc.

Step 7: Utilize the SQLite Expert Extension for Index Recommendations

Run the .expert command in the SQLite CLI to receive index suggestions:

.expert
SELECT MAX(winner_trophies) FROM battles WHERE winner_tag = 'PLAYER_TAG_EXAMPLE';

This provides recommendations tailored to the query’s structure.

Step 8: Consider Materialized Views for Frequent Updates

If max_trophies needs frequent updating, precompute the values into a temporary table and join against it:

CREATE TEMP TABLE player_max_trophies AS
SELECT 
  player_tag,
  MAX(trophies) AS max_trophies
FROM (
  SELECT winner_tag AS player_tag, winner_trophies AS trophies FROM battles
  UNION ALL
  SELECT loser_tag AS player_tag, loser_trophies AS trophies FROM battles
)
GROUP BY player_tag;

UPDATE players
SET max_trophies = (
  SELECT max_trophies 
  FROM player_max_trophies 
  WHERE player_tag = players.player_tag
)
WHERE EXISTS (
  SELECT 1 
  FROM player_max_trophies 
  WHERE player_tag = players.player_tag
    AND max_trophies > CAST(players.max_trophies AS INTEGER)
);

This reduces redundant computation by aggregating all trophy values upfront.

By addressing indexing deficiencies, rewriting the update logic to minimize full scans, correcting data types, and leveraging SQLite’s query planning tools, the performance of the max_trophies update can be improved from hours to seconds or milliseconds, depending on dataset size.