A product list might display a name and a price while each product record also contains a long description, supplier details, and internal notes. Asking for only the displayed fields is a sensible starting point. It does not, by itself, explain how the database finds the products.
Two separate questions matter: which records must the engine locate, and which fields must it return?
Projection controls the fields
Column selection, also called projection, specifies the fields in the result. An application can ask for name and price rather than every field.
This can reduce the amount of data materialized, serialized, and transferred. Whether it also reduces disk reads depends on the storage layout and execution path. A smaller response is not proof that the database performed less physical I/O.
iniBase provides a concrete example. Its documentation describes separate files for columns and a columns option in its TypeScript read API. That makes the relationship between requested fields and storage particularly visible. It still does not establish the cost of every filtered query.
An index helps locate records
Now imagine looking for a particular product code among many records. Returning only two fields does not automatically prevent a scan of the field used in the filter.
An appropriate index can provide a way to locate matching entries without examining every record. SQLite's query-planning documentation illustrates this distinction through scans, indexed lookups, and covering indexes. A covering index contains the data needed for a query and can avoid an additional table lookup.
These techniques can work together: an access path helps find records, while projection limits the requested fields. Neither technique alone describes the complete query plan.
Compare two different screens
For a broad export of product names and prices, reading a small number of columns may be useful. For a lookup of one rare product code, the way records are located may dominate. Sorting, filters, caches, and the number of matching rows can change the outcome.
That is why "column-based" and "indexed" are not interchangeable performance labels. A storage choice can fit one access pattern well and still involve compromises for another.
Measure the work your application actually does
Start with two requests against the same dataset: one returning complete records and one returning only the fields the screen uses. Keep filters, ordering, pagination, and durability settings fixed.
Then vary the selectivity of the filter separately. Include a request that matches very few records and one that matches many. Record latency, response bytes, CPU time, and peak process memory. Distinguish a cold start from a warmed cache; do not label a run "cold" unless the relevant cache state is actually controlled.
Also separate database timings from HTTP serialization and network time. A faster database call does not guarantee a faster end-to-end request, and smaller allocated disk usage does not establish lower peak RAM.
Carry the distinction into the CMS
iniContent, the Nuxt-based CMS above iniBase, is an example of where these questions become practical: a table screen and a detailed editor can need different fields. Whether a particular screen sends a projected request must be checked in its implementation; the database capability alone does not prove that it does.
For clarity, iniContent's client is Apache-2.0, while its backend API is proprietary.
The useful habit is to ask what work each layer avoids, then measure it. Requesting fewer fields and finding fewer records are both valuable ideas, but they solve different parts of the problem.
Technical references
SQLite query planning, including covering indexes: sqlite.org/queryplanner.html
iniBase storage description and read API: inicontent/inibase
iniContent architecture and licensing: inicontent/inicontent
Top comments (0)