MySQL – MySQL Query Optimization and Performance Tuning – Online Video Courses

In year 2013, I had focused on MySQL along with SQL Server in database products. As I learn more and more MySQL, I quickly realized that there is no proper course or structured information for MySQL Performance Tuning. I had decided to build a course around common MySQL Performance Problems. I have decided to build the performance tuning course in two parts. 1) MySQL Indexing for Performance and 2) MySQL Query Optimization and Performance Tuning. I had released MySQL Indexing for Performance course earlier this year and right before the end of the year 2013 the second course MySQL Query Optimization and Performance Tuning has released as well.

MySQL Query Optimization and Performance Tuning

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.

Here is the broad outline of the course MySQL Query Optimization and Performance Tuning.

Introduction

In this module we go over why MySQL Performance Tuning is different from other database products. We also explained with the help of few interesting analogies where exactly we should look into when there is a problem with MySQL Performance.

Optimizing Data Access

We cannot directly jump into performance tuning before taking care of a few of the best practices around MySQL. In this module we discuss few of the best practices which one must implement various techniques explained in this course.

In this module we will see the following topics in depth:

• Understanding Data Needs
• Demo: Setup
• Demo: Best Practices for Data Access
• Demo: Why SELECT Star (*) is a Bad Idea

Understanding MySQL Query Optimization

It is very important to understand the internals of the car if we are going to participate in a car race. Similarly it is important to understand what is under MySQL Query Engine if we are going to do query optimization with MySQL.

In this module we will see the following topics in depth:

• Execution Path of a Query
• Client Protocols
• Query Cache
• Parser
• Preprocessor
• Query Optimizer
• Query Optimizer Responsibilities
• Query Optimizer Limitations
• Query Execution Engine and Storage
• Returning Result to Client
• Additional Notes on Query Optimizer
• Maximizing Query Optimizer Performance
• Understanding Query States
• Demo: Show Full Processlist
• Understanding Explain Command
• Demo: Explain Command
• Demo: Explain Extended Command

Performance Optimization by Practical Query Tuning

In the real world we get thousands of the different scenario where the performance of our query is not upto the mark. In this module we will discuss the most common query patterns which reduces the performance of any query. In this demo oriented session we will also understand how we can tune the performance of bad query with the help of query rewrite and by implementing performance best practices.

In this module we will see the following topics in depth:

• Demo: Index Used for SELECT clause
• Demo: One Complex Query vs Multiple Simple Queries
• Demo: One Complex Query vs Multiple Simple Queries – Part 2
• Demo: One Complex Query vs Multiple Simple Queries with Index
• Demo: Table Order in Join Clause – INNER JOIN
• Demo: Table Order in Join Clause – OUTER JOIN
• Demo: Most Optimal Choice – Subquery vs Exists vs Joins
• Demo: Most Optimal Choice – Subquery vs Exists vs Joins – Part 2
• Demo: Tuning Aggregate Function
• Demo: Optimizing Group By Clause
• Demo: Optimizing Paging with LIMIT Clause
• Demo: Impact on Performance of UNION and UNION ALL
• Demo: Index and Not Equal to Operator

Best Practices

In this final module we sum up the course in the format of Best Practices. Though the length of this module is short, it contains some really good gem inside it.

You need a valid Pluralsight subscription to watch the courses. You can sign up for free trial as well.

Reference: Pinal Dave (https://blog.sqlauthority.com)