Optimization tales with CockroachDB: the slow password reset flow (part 1)

This article details the investigation and optimization of a slow password reset SQL query in CockroachDB, part of the Kratos identity management service. The query initially read 700k rows, but through three key optimizations—providing the 'via' column, restricting to a single region, and selecting only needed columns—the row count was reduced to about 10. The author discusses query execution statistics, indexing, and differences across databases (SQLite, PostgreSQL, MySQL, CockroachDB). This is part 1 of a series on SQL optimization, aimed at developers working with authentication systems and database performance tuning.