MySQL Query Optimization and Performance Tuning

This course focuses on Query Optimization and Performance Tuning of MySQL.
Course info
Rating
(132)
Level
Intermediate
Updated
Dec 30, 2013
Duration
1h 50m
Table of contents
Introduction
Optimizing Data Access
Understanding MySQL Query Optimization
Performance Optimization by Practical Query Tuning
Best Practices
Description
Course info
Rating
(132)
Level
Intermediate
Updated
Dec 30, 2013
Duration
1h 50m
Description

Performance is one of the most essential aspects of any application. Everyone wants their server to perform optimally and at the best efficiency. In this course we will understand the basics of query optimization and look at practical tips and tricks for performance tuning.

About the author
About the author

Pinal Dave is a Pluralsight Developer Evangelist.

More from the author
Getting Started with MariaDB
Beginner
2h 23m
Nov 2, 2018
Monitoring MySQL with Performance Schema
Intermediate
1h 59m
Apr 28, 2017
More courses by Pinal Dave
Section Introduction Transcripts
Section Introduction Transcripts

Introduction
MySQL Query Optimization and Performance Tuning. Hi, this is Pinal Dave for Pluralsight. Welcome to MySQL Query Optimization and Performance Tuning course. We'll be working together on this journey to tune MySQL Queries. Let us assume that we are going on a long road trip. There are a few basic things we always remember like the ties of the car should have proper air pressure, we should fill up the tank with fuel, and the engine should be well oiled. You start your journey and it starts smooth. Now all of the sudden, your car starts to slow down without any reason. You get out of the car and check tire pressure, fuel level or oil level in the engine, but sometimes there is more to it than what we see on our very first look. We need to go beyond the basic best practices and understand the inner working of the engine to find the real fault in the car. Once we know the engine and its inner working, we can find out the real problem and fix it. This course is also organized based on the same philosophy. First, we'll be looking at the best practices for performance optimization. We will learn about what are the things _____ and developers should keep in mind before even starting performance tuning. Once we learn the best practices to implement for basic performance of any query, we will learn about the various stages and their functions. Next, we will focus on the tuning of queries, which developer often writes in the real-world scenarios. We will explore various practices for measuring performance and _____. Well, I'm very confident that you will enjoy the course. We will be discussing about Optimizing Data Access, Understanding MySQL Query Optimization, and Performance Optimization by Practical Query Tuning. Let's start our journey.

Optimizing Data Access
Hi, this is Pinal Dave, and welcome to the module of Optimizing Data Access. I often see in _____ that most of the queries are retrieving more data than they needed. It may come as a surprise to many, but when developers are writing queries hardly they get the exact requirements of the data to the trial. As the requirements are often changing, developers have to rewrite many queries to adjust the new needs. Amid all this developer often ends up writing queries, which not only satisfy their current needs, but also include a few data, which they anticipate to be used in the near future. If their guess is correct, they do not have write new queries and avoid changing code, however, there are chances that the developer has incorrectly predicted the future, and the query now retrieves more data than it requires. It is very crucial for developers to understand the proper data needs and implement best practices for data retrieval before Query Tuning phase. In this module, we will discuss a few of the best practices related to data access.

Understanding MySQL Query Optimization
Hi, this is Pinal Dave and in this module we will understand MySQL Query Optimization. In Formula one race, every racer wants to win, but there are two key important variables which are deciding factors in the decision of the winner, the skill of the racer and the engine of the car. Query optimization is just such a race. For any query to run faster, there are two important factors, the skill of the developer and MySQL Query Optimizer Engine. In this module, we will understand how MySQL Query Optimizer Engine works so we can use this additional knowledge to further improve our skills and write extreme fast performing queries. We will discuss about query execution path, maximizing query optimizer performance, understanding query states, query optimizer responsibilities and limitations, and before we conclude the module we will understand how EXPLAIN command works.

Performance Optimization by Practical Query Tuning
Hi, this is Pinal Dave, and in this module we will understand Performance Optimization by Practical Query Tuning. In an earlier module, we have learned what are the best practices we should implement before starting performance tuning and we understood how the Query Optimizer works. In this module, we will be covering concepts which directly impact the performance of the query. In this module, we will see various practical examples of how query optimization works in MySQL. In MySQL, there is no out-of-the-box tool to measure performance of the query. Hence, we will be using various techniques to validate performance gain of a query. In this demo-oriented module, we will see that every query is different and there are different methods to tune each of them. We will cover in this module the following most frequently encountered issues by developer. With the help of demonstration, we will understand how to tune Joins, Subqueries, Union, Aggregated Functions, Grouping, and Indexing. We will also understand what are the best practices a developer should remember when writing any MySQL query. Remember that we have covered indexes in our earlier course of MySQL Indexing for Performance. Hence, we will only cover a few of the essential concepts related to indexes in this course. I recommend to watch MySQL Indexing for Performance for additional in-depth learning related to indexes. Now let us directly jump into demonstration and learn how to tune MySQL queries.