- Indexes are used in databases to speed up reads. Any kind of index usually slows down writes, because the index also needs to be updated every time data is written.
A clustered index stores all row data within the index.
A non-clustered index only stores the references (to heap file) to the data within the index.
A covering index stores some of a table's columns within the index. This allows some queries to be answered using the index alone.
Indexes duplicate data and although they speed up reads, they require additional storage and can add overhead on writes.
Multi-column indexes enables efficient querying of multiple columns at once. e.g. a multi-column index on (date, temp) can be used to efficiently find all observations in 2013 where the temp was between 25 and 30 degrees. With a 1D index, you would have to either scan all records from 2013 and then filter (or vice vera).
In memory databases
The performance advantage of in-memory databases is not due to the fact that they don't need to read from disk - even a disk-based storage engine may not need to read from disk if there is enough memory since the OS caches recently used disk blocks in memory anyway. The performance advantage comes from not having to encode in-memory data structures in a format that can be written to disk.
OLTP vs OLAP access pattern
OLTP (Online Transaction Processing) is an access pattern that is widely used in interactive applications where records are inserted/updated based on user input e.g. actions in a game.
OLAP (Online Analytics Processing) is an access pattern used for analytic queries and business intelligence.
Top comments (0)