MySQL is the most popular and has been using by many Industry-leading companies such as Facebook, LinkedIn, Twitter & YouTube. Moreover, so many start-ups, Small & middle-size companies are using. MySQL Performance is one of the most common issues facing by many companies. Mainly, start-ups, Small & middle-size companies. In fact, many projects have been developing without MySQL Performance expert or MySQL architect. Generally, they are experts in database design & index optimization.
Query Optimization is one of the key areas to boot your MySQL DB performance. Generally, It supposed to done by MySQL performance experts. but it’s happening only on industry-leading companies and high budget projects. In this blog, I will explain a simple and easy way to do MySQL Query Optimization and boot your MySQL production server more than 100X.
Common Mistakes in MySQL Query Optimization
If the project doesn’t have a DB performance expert then this will be done by the Application team, DBA or Linux Admins. They are not experts in Query Optimization and apply simple & easy approaches to select columns for the index for optimizing the problematic queries. The following approaches are used by them what we have noticed
- Create an index to Select all columns in the same order or reverse order in the query.
- Create an individual index on all columns in the query.
- Create an index on randomly selected columns in the query.
Truly speaking, It is true from their point of view. In fact, sometimes these approaches work for better than FULL TABLE SCAN queries. After that, It builds more confidence in these approaches. But, for the long-run, these approaches will create many DB production issues as below.
- Dead Locks
- Long-Running Queries
- Query Locking
- DB Server Hanging
- High CPU spikes
- High RAM spikes
- High Disk I/O spikes
- Poor query response time
- Table Fragmentation
- Slow down INSERT, UPDATE, and DELETE commands
- Space issues with Duplicate and unused indexes
- Frequently reboot DB server
If your Production DB is facing any one of these issues then the root cause is Long-running SQL Queries which need Query Optimization to select an optimal index to fix permanently these issues.
What is the Optimal Index?
MySQL Optimizer uses only one index per table in most of the cases. There will be N+N!*(N-1) indexes combinations that are available for the query. Here N is no.of unique columns in where condition filter column, group by and Order by columns. All columns belong to the same table and N >=1. If the query is using more than one table then we need to use the same formula for each table then multiply the final results.
There is only one optimal index per table per query which can scan fewer records to return the result not more than 120% of return records count. It is nothing but an Optimal index. There is very less probability to select the optimal index using shortcut /easy methods by nonprofessionals. Let me explain more details
- If N=1 then 1+1!*(0) = 1+1*(0)=1 possible index
- If N=2 then 2+2!*(1) = 2+1*1*(1)= 4 possible Indexes
- If N=3 then 3+3!*(2) = 3+3*2*(2)=15 possible indexes
- If N=4 then 4+4!*(3) = 76 possible indexes
- If N=5 then 5+5!*(4) = 485 possible indexes.
- If N=6 then 6+6!*(5) = 3606 possible indexes.
If the query has only one column in WHERE clause then there is only one possible and it is nothing but optimal indexes. After that Optimal indexes selection become difficult and probability also gradually reduce and become zero based on the query complexity
Examples:
In this example, if the user creates an index on the “SAL” column and the table has more than 50% records salary greater than 2000 then the query will scan 50% records to return results set which is one or two records. It is not a good index for DB to scan your query 50% records for getting 2 records. Even if you create a composite index on the same order or reverse order it is not the optimal index. The root cause is the rang filter(sal>200) always should be the last position because index conditional push will not work after rang filter. And the other column filters positions we should select based on the column’s cardinality.
How to Check out your Production MySQL server SQL Queries use optimal indexes or not?
We can get executed queries or query templates scan rows count and return rows count using MySQL Sow query log file or sys.statement_analysis View(If enable PS).
If your queries are scanning just less than 120% of total return records then the queries have good indexes/ optimal indexes. Otherwise, It needs series action and more chances to improve performance after adding an optimal index.
If your Production DB is facing any one of the following issues that indicate your DB server SQL queries are not properly designed indexes
How to MySQL Query Optimizer?
However, MySQL saves how many rows scans and how many rows returns for a query in MySQL slow query log file and sys.statement_analysis view. In fact, if queries are scanning rows more than 120% of total return records then the queries don’t have good indexes/ optimal indexes.
Also, If your DB is serving live traffic and frequently triggers production issues such as deadlock, Query locking, Replication lagging, bad query response time, DB server hanging with High CPU usage, high RAM, high Disk IO usage problems which are slow down DB server performance indicates SQL queries don’t have optimal indexes.
Query Optimization Tools for MySQL
There are MySQL Query Optimization tools available in the market since 2017. My Favorite tool is the SmartWorkbnech tool. Moreover, it can tune SQL queries and highly helpful for Developers and DBAs. It is the MySQL GUI tool and you can optimize your SQL queries while writing SQL. Also it can optimize SQL queries from a slow query log file and sys.statement_analysis
Query optimization:
The tool will analyze the query and give a warning after entering semicolon(;) at end of the query if the query doesn’t have an optimal index or scan all records.
After that, If you click that warning icon(1) or Query Optimizer icon (2) Then it will open the Query Optimizer module.
Next, Run the analyzer(3) button then it will recommend optimal indexes(4) , Query rewrite recommendations(5,7) and expected performance improvement(6). After that, if you click the “Create Index” button then it will add recommended indexes and provide a consolidated performance improvement report with all details such as percentage of improvement in scanning records, query execution time improvement for each query execution state.
How to Query Optimize by DBA’s?
There is one more module called the Slow Query analyzer (1). You can upload your MySQL slow Query log files(3) or drag and drop files(2). Also, if you enable OS connection then it can pull from OS(4).
It can give a consolidated report which can help to identify which query is executing with high frequency, which query is taking more time for execution and which query is waiting for DB locks. It is can do Query optimization for bulk queries and query optimize all of them at once.
Query Rating: it calculates query ratting based on how many rows scanning and how many rows it returns. The red stars indicate the Query is needed query Optimization.
Query: It is a Query template
Response time: it is the total response time
Pct(): calculate query response percentage based on total response time
Avg Response Time: It is Avg response time
QEDT: Query Execution Date & Time Graph
QETI : Query Execution Time Interval Graph
And it has much other information such as min, max, stddev response time, Lock time, Rows sent and rows examined.
If you select SQL query Optimizer tab then it will run the Query optimizer for each query template and provide a list of recommended optimal indexes for all your slow queries
Dynamic Query builder is another advanced feature and you can develop your SQL Queries 10X faster than MySQL Workbench.
