I have been reading this wonderful book that explains how database indexes
can improve on SQL query performance. If we create the right kind of indexes on our tables then the SQL keywords like
group by makes use of the indexes to fetch records matching the criteria.
The book argues that it is the developer (not the DB administrator) of an application that knows what data the application needs. But developers may not know how databases retrieve the data specified as SQL queries. It turns out that all relational databases use indexes to speed-up data retrieval.
The book talks about only one type of index, the B-Tree index. B-Tree indexes are enough to speed-up a majority of the SQL queries.
Below are the three powers of database indexes (according to the book) that help to speed-up SQL queries:
- The B-Tree traversal is the first power of indexing.
- Clustering is the second power of indexing.
- Pipelined order by is the third power of indexing.
Read the whole book to know what every developer should know about SQL performance.