This chapter starts by some general considerations for query performance, a discussion of slow queries and the things to consider first when a query is not performing as well as expected. It ends with a case study on indexes that puts everything together in a toy project.Ħ.1 Query Performance Optimization| Part 1 Then it goes on to discuss indexing strategies, such as, choosing the indexed columns, choosing the columns order, clustered indexes, covering indexes and how performance is affected by redundant, duplicate and unused indexes. This chapter starts by discussing the basics of indexes, their types, their internal structure and their benefits.

Indexing optimization is the most powerful way to improve query performance as choosing the correct indexes can improve performance by orders of magnitude in many cases, so it's essential to have a good understanding of indexes and how they work and how to employ them to your benefit. Having indexes is critical for good performance specially as the database grows. This is, in my opinion, the most important chapter in the series because designing a good and an efficient schema is not the whole story. The chapter starts with a discussion of available data types in MySQL and how to choose the optimal data type for your use case, then it goes into a discussion of some choices when it comes to the logical design of a schema such as normalized and denormalized schema designs, then it discusses how to mix both design approaches to speed up queries by using techniques such as cache and summary tables and since no schema stays the same, the chapter ends with some advice on the use of ALTER TABLE statements. This chapters teaches you schema design for good performance. This chapter teaches how to profile your MySQL server performance, it starts by a discussion of profiling means, then it moves to discuss topics like application instrumentation, profiling the server workload, profiling single queries, useful tips on how to profile and detect slow queries and it ends with a detailed case study of how profiling can help detect server problems. It starts by a discussion of what benchmarking means, the different possible benchmarking strategies and the available benchmarking tools that you can utilize. This chapter teaches you how to methodically benchmark your MySQL instance performance. Many of those topics have dedicated chapters to them so they will only be discussed briefly in this chapter. It starts by a discussion of MySQL's logical server architecture, it proceeds to discuss topics such as connection management, query optimization and execution, concurrency control, transactions, isolation levels and finally a discussion of the different MySQL storage engines. This chapter provides a high-level overview of the MySQL server architecture. The series I have published discusses only the first 7 chapters which are in my opinion that most essentials ones in the book. The book goes over a lot of concepts when it comes to database internals as you will get to learn a lot about how different components are designed which will give you a pretty good introduction that's very important to understanding any discussed optimization techniques. High performance MySQL takes you on a journey of learning advanced techniques for everything from designing schemas, indexes, and queries to tuning your MySQL server, operating system, and hardware to their fullest potential.

An overview of what each chapter will teach you.I have only posted about the first 7 chapters which I think are the most essential ones for anyone learning relational databases and especially MySQL. Over the course of the past few months, I have been publishing articles summarizing a really nice book called High Performance MySQL.

Mysql profiling and query optimization