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)
2 Comments. Leave new
Hi Pinal, I went through the tutorials on PluralSight but still I have few doubts.
To optimise a search as fulfillment_order_notes not LIKE ‘%fraud%’ , I added a column in table and updated it with 0 wherever fulfillment_order_notes was not LIKE ‘%fraud%’. Then instead of doing fulfillment_order_notes not LIKE ‘%fraud%’, I did ‘IsFraud=0’.
I thought this will improve performance but it didn’t. Can you suggest.
Regards
Thanks Suman,
I appreciate you asking this question but with this much little information, it is difficult to answer this question.