When you look at a query, such as
SELECT * FROM trips WHERE bike_id = 'x', you can't actually tell if it executes efficiently. This is because SQL is declarative and it defines what the result or the output should look like. It is not procedural or a set of steps on how to produce that result. For example, do x, then do y, then combine the two.
In many ways, an SQL query is like a physical address that you type into GPS navigation. The GPS navigator figures out the procedure or set of navigation steps. You are just defining the final location. And a good navigation system takes you on the most efficient route. We expect very similar properties between these two systems: GPS navigation and query optimization.
But where this analogy breaks down a little, is that with a database management system, we can add our own highways and effectively lay the data out in such a way that the query can be more efficient. I am of course talking about features like indexes and table partitioning.
But we will build up to that. Query optimization all starts with asking the database management system to explain to you how it plans to execute a particular query.
And on that note let‘s take a tour of the EXPLAIN command…