Q22 of 26 · SQL
When would a QA engineer care about database indexes, and how do indexes affect test execution speed?
Short answer
Short answer: Indexes make queries on large tables fast by avoiding full table scans. QA engineers care when test-data setup/teardown queries are slow, when validating that the app creates indexes for its expected query patterns, and during performance testing.
Detail
Why QA engineers encounter indexes:
Slow test setup — a DELETE FROM orders WHERE customer_id = 99999 on a 10M-row table takes 30 seconds without an index on customer_id. With an index: milliseconds.
Verifying the schema is correct — if the app spec says "search by email must be fast", there should be an index on users.email. QA can verify:
-- PostgreSQL
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';
-- MySQL
SHOW INDEX FROM users;
- Performance testing data integrity — confirming the execution plan uses the expected index under realistic data volumes:
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';
-- Should show "Index Scan" not "Seq Scan"
Index types to know:
- B-tree (default) — equality and range queries (=, <, >, BETWEEN, LIKE 'prefix%')
- Hash — equality only, faster than B-tree for = but useless for range queries
- Partial index — indexes only a subset of rows (e.g., WHERE status = 'active'), smaller and faster for specific filter patterns
QA gotcha: Adding data to a large table in tests without going through the indexed columns in WHERE clauses means cleanup queries will be slow. Design test-data IDs to be in the indexed column.