Introduction
SQL presents data as tables containing rows and columns. Behind that logical view, the database organizes records into pages, caches useful pages in memory, and uses indexes to locate data efficiently.
These structures explain why two queries returning the same number of rows can perform very differently. Understanding their relationship also shows why primary-key choices and additional indexes affect more than lookup speed.
Where Rows Actually Live
In a typical page-based database, table storage is organized into files containing pages, with records stored inside those pages.
A page has a defined size for the storage configuration. As data grows, the database allocates additional pages rather than making each page larger.
When a query needs one record, the database generally accesses the page containing it. That page may also contain other records.
This distinction matters because:
One page access can make several records available.
Wider records generally leave room for fewer records per page.
Large column values may require additional storage.
Returning a few rows can still involve examining many pages.
Exact file layouts and page sizes depend on the database, but page access is a central part of storage performance.
Why Buffers Make Reads Faster
A buffer pool holds database pages in RAM so that queries can reuse them.
Suppose Page P0 contains records R1 and R3, while Page P1 contains R5. A query requesting all three records needs access to those two pages.
Situation | What happens |
|---|---|
Required page is buffered | The database uses the page already in RAM |
Required page is absent | The database loads it into a buffer |
Buffer space is needed | A replacement policy chooses a suitable page to remove |
These situations are commonly called a buffer hit and a buffer miss.
A miss does not always require a physical device read because another caching layer may supply the page.
Frequently accessed pages can benefit later queries while they remain cached. However, memory is limited, so the database cannot keep every page indefinitely.
Pages and the buffer pool
How Keys Connect Indexes to Records
Indexes provide organized access paths to stored records. A B+ Tree index uses internal pages for navigation and leaf pages for data entries.
What those leaf entries contain depends on the storage design.
For this example, consider MySQL’s InnoDB storage engine, where an explicit primary key organizes the clustered index:
Structure | Simplified leaf contents |
|---|---|
Clustered index on | Primary key and row data |
Secondary index on | Email and primary key |
This behavior is specific to that storage design; it is not a universal rule for every database.
Suppose the table is:
id | country | |
|---|---|---|
101 | India | |
102 | Nepal |
An email index can locate the matching primary key, which then leads to the row.
Following a Query’s Access Path
Consider:
SELECT countryFROM employeesWHERE email = 'asha@example.com';Assume email has a secondary index and the database chooses it.
The simplified lookup is:
Search the email index.
Find the entry containing
asha@example.comand primary key101.Use
101to search the clustered index.Read
countryfrom the matching row.
The required index and data pages may already be buffered. Two index traversals do not necessarily mean two physical disk reads.
Now change the requested column:
SELECT idFROM employeesWHERE email = 'asha@example.com';The email index already contains both the search value and the requested ID. It is therefore covering for this query and can avoid the additional row lookup in the usual case. Transaction visibility checks can still require extra work.
Covering describes the relationship between an index and a particular query. The same index may cover one query but require additional row lookups for another.
Secondary Lookup vs Covering Query
Why Primary-Key Width Matters
In the illustrated storage design, secondary-index entries carry the primary key. A wider primary key therefore increases the size of those entries across multiple indexes.
Larger entries can mean fewer entries per page, more pages to store and cache, and additional maintenance work.
Primary keys can also be repeated in related tables as foreign keys. Their width and stability consequently matter across the schema.
Choose a key that is unique, appropriately sized, and unlikely to change. A compact internal ID can be useful, but it does not replace necessary business constraints such as unique email addresses.
Writes Change More Than One Structure
An insert adds a record and the relevant index entries. A delete removes entries, while an update requires index maintenance when indexed values change.
In a typical buffered write path, modified pages become dirty pages: their changes have not yet been written back to their persistent page locations.
Recovery logging helps protect changes while data-page writes are deferred. With durable commit settings, required log information is persisted before success is acknowledged; the changed data pages do not all need to be flushed at commit.
Additional indexes therefore introduce ongoing storage and write costs, even when they improve important reads.
Connecting the Design to Performance
Before adding an index, identify the expensive work:
Is the query examining many records to return only a few?
Does it perform repeated row lookups?
Could an existing index cover the required columns?
Are useful pages being reused in memory?
How much maintenance would another index add?
Use execution plans and realistic measurements to evaluate changes. A table scan can be appropriate when a query needs much of the table, and an extra index is not automatically an improvement.
Summary
Records live inside pages, buffers make pages available in memory, and indexes guide access to the required entries. Storage organization determines how those entries connect to the actual rows.
Key width, covering queries, and index maintenance all influence the amount of work performed. Effective tuning considers page access and the complete workload rather than treating every query as an isolated lookup.
Be the first to add a comment.