In this chapter, we covered the process of finding slow queries and optimizing them.

First, we analyzed how the slow query log can be configured to find queries that we want to be faster. Its options allow defining useful criteria. We also discussed the pt-query-digest tool, from the Percona Toolkit, that gave us an overview about the performance of slow queries.

We discussed how MariaDB uses indexes. This is very important to know because a fast query is generally a query that makes good use of a proper index. Then, we discussed an important feature of MariaDB 10.0 called engine-independent statistics, which collects statistics about data in the indexed and non-indexed columns. These statistics can be used by the optimizer to choose a query execution plan.

In the last part, we analyzed the EXPLAIN statement. This command provides information about the strategy the optimizer decided to use to execute a query. While discussing EXPLAIN, we also analyzed the main execution plans that MariaDB uses to execute queries.

Based on the EXPLAIN output and knowledge of the possible execution plans, we can adjust the indexes or the query so that MariaDB can use a better plan.

Of course, in an environment where concurrency exists, individually optimizing the queries can be insufficient. The next chapter discusses how transactions prevent sessions from colliding, and how to minimize the overhead caused by the isolation of these transactions.