Back to articles list
Some time ago, the Vertabelo Team participated in the PostgreSQL Conference Europe 2013. Some of the talks were really nice. One of them stuck in my head for quite a long time. It was Markus Winand’s lecture titled “Indexes: The neglected performance all-rounder.” Although I had had a solid background in databases, this 50 minutes long talk showed me that not everything concerning indexes was as clear to me as I had thought. This was the kind of lecture I like the most – when you sit and say to yourself “damn, I didn’t know that!”
Books vs. indexes
Markus made a simple study – he checked out how often books mention indexes. The results are pretty scary:
“11 SQL books analyzed: only 1.0% of the pages are about indexes (70 out of 7330 pages).”
“14 database administration books analyzed: 5.1% of the pages are about indexes (307 out of 6069 pages).”
(source: http://www.slideshare.net/MarkusWinand/indexes-neglectedperformanceallrounder)
As far as I remember, Markus said that this was one of the drivers that pushed him towards writing his own book. He only mentioned it in passing, but this was the moment that made me want to read this book. Database performance is such an important issue that it’s never a bad idea to read about it one more time. It took me a few months to find some time for this book but I don’t regret one second spent on it! Winand’s SQL Performance Explained is quite short, but condensed in such a way that every single page gives you pure knowledge.
SQL Performance Explained – the book about indexes
Markus Winand’s SQL Performance Explained embarrasses all of the previously mentioned books. This is the book about indexes: describing their construction and usage. The author explains when and, most importantly, why an index may or cannot be used by a database in specific queries.
The book covers such issues as an index use in queries with an equality operator, the LIKE clause, functions, range operators and many more. A very important part of the book is its description of different algorithms used in a join operation. It also explains when databases perform an index-only scan and why index-organized tables are not as good solution as they may at first seem. And, last but not least, Winand explains the EXPLAIN PLAN in detail. After reading the book, you should be able to understand why your DBMS didn’t choose to use an index and what you can or should do to make your query run faster. The number of new questions you post to stackoverflow.com may drop significantly ;)
This book will not satisfy you if you already know:
- when DBMS uses and when it doesn’t use indexes,
- why DBMS sometimes chooses FULL TABLE SCAN even if there’s an index it could use,
- how to read explain plans of Oracle, PostgreSQL, SQL Server and MySQL,
- how prepared statements affect query execution time,
- the impact of indexes on DML statements.
If you’re not familiar or don’t feel comfortable with any of the above, I really recommend you read this book. It’s worth mentioning that you may either buy it, or... read it online for free!
What more can I say than: great job, Markus!
