Indexing & Query Optimization

Indexing & Query Optimization#

A query scanning 50 million rows to return 3 records is not a slow query. It's a schema design failure. This exact scenario plays out when an orders table grows to tens of millions of rows and someone queries it by user_id without an index. The database reads every page on disk, one by one, because it has no other choice. The query was probably fine at 10,000 rows. Nobody noticed until product...