Without an index, finding the rows that match a condition means reading the entire table (a full table scan).
Why A new feature ships with a search on a condition that has no index → Effect Scanning millions of rows makes a single query take hundreds of ms to several seconds → On screen Mailbox and trade history load slowly, and tied-up connections make other requests wait too
Primary owner Game team (Server development) · Also Infra team (DB infrastructure)
Game team action items
Review the query plan of every new query before deploying, add indexes, check that modifying queries (UPDATE, DELETE) use an index too.
Infra team action items
Watch the slow query log, find full-table-scan queries and share them with the game team, add indexes in production with online methods that hold locks only briefly.
Ballpark numbers
With an index, a few ms. Without one, the query slows down in proportion to data size, which on a large table means hundreds to tens of thousands of times slower.
On the graph
Step change · DB query latency, rows read
Where to look
MySQL: Rows_examined and Rows_sent in the slow query log (with log_queries_not_using_indexes on, queries that don’t use an index are logged too), SUM_NO_INDEX_USED and SUM_ROWS_EXAMINED in performance_schema events_statements_summary_by_digest, then run EXPLAIN. PostgreSQL: seq_scan and seq_tup_read in pg_stat_user_tables, then run EXPLAIN
Confirmed if
A query that appeared after the deploy reads thousands of times more rows (Rows_examined) than it returns (Rows_sent), and EXPLAIN shows a full table scan (MySQL type ALL, PostgreSQL Seq Scan). seq_tup_read on a large table climbs steeply from the deploy time
Ruled out if
Slow even while using an index: lock waits (db-hot-row, db-ddl-lock) or a query plan change (db-plan-flip). A full scan of a small table can be normal
Check with
Infra tools (no game code needed)
Learn more
Reads aren’t the only thing that slows down. A modifying query (UPDATE, DELETE) without an index can, depending on the DB, lock every row it scans and block saves for unrelated players.
Sources
How MySQL Uses IndexesMySQL Without an index, the whole table is read starting from the first row, and the bigger the table, the higher the cost
The Slow Query LogMySQL Logs queries that exceed long_query_time (default 10 seconds); queries that don’t use indexes can also be logged separately
CREATE INDEX (PostgreSQL Documentation)PostgreSQL Building with CONCURRENTLY creates the index without blocking writes; a regular build blocks writes until it finishes
Statement Summary TablesMySQL events_statements_summary_by_digest: SUM_NO_INDEX_USED (times executed without an index) and SUM_ROWS_EXAMINED per normalized query
EXPLAIN Output FormatMySQL type ALL means a full table scan, usually avoided by adding an index