How Relational Databases Find Database: Understanding Table and Index Scans

When creating indexes it's important to understand how they work. In my previous post , I've explained the basics of indexes and the EXPLAIN command. Now we can go a step further and look at the different types of reads a database can perform. Understanding these access patterns is crucial because it helps us evaluate the trade-offs of creating (or not creating) an indexes. It also gives us insight into what happens under the hood when a query is executed. At a high level, databases retrieve data in two main ways: Scanning the table directly (reading rows sequentially); Using an index to locate rows more efficiently; The query planner decides which strategy to use based on statistics, table size, and how selective the query conditions are. Since different databases use slightly different terminology, I've included the names used by each database engine for reference. 1. Full table scan (FTS) Oracle DB: Full table scan SQL Server: Table Scan MySQL: Table Scan PostgreSQL: Sequential scan