Despite indexes often improving performance, there are important cases where a full table scan is actually better than using an index.
✅ When Is a Full Table Scan More Efficient?
1. When a Large Portion of the Table Matches
- If a query needs to scan a high percentage of rows (e.g., >10–20%), a sequential scan is often faster than jumping around via an index.
✅ Example:
SELECT * FROM Orders WHERE order_date > '2000-01-01';
if 90% of the table matches, the index offers little benefit — the DB just scans the whole table efficiently.
2. Low Selectivity (Few Distinct Values)
- If you filter on a low-cardinality column, the index doesn’t help much.
✅ Example:
SELECT * FROM Users WHERE gender = 'M';
If half of all rows are 'M', the DB will often choose a full scan.
3. Fetching All or Most Columns
- Indexes store only some columns.
- If you’re selecting all columns, the database may do an index lookup + row fetch for each row, which is slower than just reading the table directly.
✅ Example:
SELECT * FROM Products WHERE price > 10;
Even if there’s an index on price, if all columns are needed, the index might cause extra disk reads (called a “bookmark lookup”).
4. Small Tables
- For small tables, it’s often faster to scan the whole thing than to use an index.
- Index overhead may not be worth it for a 10-row table.
5. Outdated or Non-existent Statistics
- If the database query planner lacks good stats, it might default to a full scan.
- Also true if no suitable index exists.
6. Queries with Functions or Calculations on Indexed Columns
- Expressions disable index use unless functional indexes exist.
❌ Example:
SELECT * FROM Users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';
📊 Summary Table
| Scenario | Index? | Full Scan? |
|---|---|---|
| Small table | ❌ | ✅ |
| Most rows match (low selectivity) | ❌ | ✅ |
| Query returns many/all columns | ❌ | ✅ |
| High selectivity (few rows match) | ✅ | ❌ |
| WHERE on low-cardinality column | ❌ | ✅ |
| Complex expressions in WHERE clause | ❌ | ✅ (unless function index) |