Sign up FAST! Login

It turns out that the only thing developers need to learn is how to index.

SQL performance problems are as old as SQL itself—some might even say that SQL is inherently slow. Although this might have been true in the early days of SQL, it is definitely not true anymore. Nevertheless SQL performance problems are still commonplace. How does this happen?

obiwan lego

It turns out that the only thing developers need to learn is how to index. Database indexing is, in fact, a development task. That is because the most important information for proper indexing is not the storage system configuration or the hardware setup. The most important information for indexing is how the application queries the data. This knowledge—about the access path—is not very accessible to database administrators (DBAs) or external consultants. Quite some time is needed to gather this information through reverse engineering of the application: development, on the other hand, has that information anyway.

This book covers everything developers need to know about indexes—and nothing more. To be more precise, the book covers the most important index type only: the B-tree index.

The structure of the book is tailor-made for developers; most chapters correspond to a particular part of an SQL statement.

CHAPTER 1 - Anatomy of an IndexThe first chapter is the only one that doesn’t cover SQL specifically; it is about the fundamental structure of an index. An understanding of the index structure is essential to following the later chapters—don’t skip this!

Although the chapter is rather short—only about eight pages—after working through the chapter you will already understand the phenomenon of slow indexes.

CHAPTER 2 - The Where ClauseThis is where we pull out all the stops. This chapter explains all aspects of the where clause, from very simple single column lookups to complex clauses for ranges and special cases such as LIKE.

This chapter makes up the main body of the book. Once you learn to use these techniques, you will write much faster SQL.

CHAPTER 3 - Performance and ScalabilityThis chapter is a little digression about performance measurements and database scalability. See why adding hardware is not the best solution to slow queries.

CHAPTER 4 - The Join OperationBack to SQL: here you will find an explanation of how to use indexes to perform a fast table join.

CHAPTER 5 - Clustering DataHave you ever wondered if there is any difference between selecting a single column or all columns? Here is the answer—along with a trick to get even better performance.

CHAPTER 6 - Sorting and GroupingEven order by and group by can use indexes.

CHAPTER 7 - Partial ResultsThis chapter explains how to benefit from a “pipelined” execution if you don’t need the full result set.

CHAPTER 8 - Insert, Delete and UpdateHow do indexes affect write performance? Indexes don’t come for free—use them wisely!

APPENDIX A - Execution PlansAsking the database how it executes a statement.

APPENDIX B - Myth DirectoryLists some common myth and explains the truth. Will be extended as the book grows.

APPENDIX C - Example SchemaAll create and insert statements for the tables from the book.

Stashed in: Big Data!, technology

To save this post, select a stash from drop-down menu or type in a new one:

Is the point that once you have an index there's lots you can do?

Or is the point that indexing is the hardest part?

You May Also Like: