Query optimizations

Now that we have a basic understanding of how to design our databases with performance in mind, we are ready to look at best practices for writing efficient queries. We will also look at query execution plans and some advanced SQL techniques. To make our example SQL statements relatable, we will use a book inventory and order processing database throughout this section.

Query execution

Understanding how queries are handled by our database is key to being able to optimize them.

A query execution plan

A query execution plan provides details on how a database engine executes queries

A query execution plan includes details on database query operations, such as joins and sorts. Let’s look at a simple query that gives us a specific book’s total sales:

FROM Orders o
JOIN Books b ON o.BookID = b.BookID
WHERE b.Title = 'High Performance with Java';

Now, let’s add an EXPLAIN command to the same query to reveal the steps the...