Generally, DB schema design, indexes creation & SQL code review suppose to done by DB Architects/DB performance experts. At the present time, all Programming languages have advanced frameworks. As an example ORM framework (Object relation Mapping). Moreover, these frameworks provide a mapping from classes to database tables and mapping from data types to SQL data types. Also, It generates RDBMS Tables, SQL code. On the positive side, It speeds up application development and relieves the application developer from manual handling. On the negative side, There are high possibilities to ignore the final schema & SQL code review by DB performance experts or DB Architects.
Often due to a lack of indexes, queries that were fast when database tables have only thousands of rows will become quite slow when the tables have millions of rows. In such circumstances, the DB server creates production problems at peak traffic times. Also, It causes service outage & impacts business.
In this blog, I will explain a simple, easy & most efficient way to improve MySQL performance. Generally, it is highly useful for applications Developers & MySQL DBAs who will try to the optimizer in case of DB architect’s unavailability time. As for My knowledge, There are three possible techniques in MySQL server optimization to improve MySQL DB performance
- SQL Queries tuning with Optimal indexing
- Database design and datatype Optimization
- MySQL Configuration Parameters Optimizing
SQL Query Optimization:
Query Optimization is nothing but selecting a good index for your queries. In fact, it is difficult to select a good index out of N!*(N-1)+N possible indexes. Here N is no.of columns in WHERE, GROUP BY and ORDER BY clauses. In the first place, it needs to create a good index for getting the best performance whenever developing the DB queries. Most impatiently, It should choose the right columns with the right order in an index based on the index behavior and their design.
Indexes are very sensitive their behavior will be change based on filter types, logical operator between filter conditions, columns cardinality, apply functions on the filter column, arithmetic operations with filter columns, JOIN condition, Grouping function, GROUP BY clause columns and ORDER BY clause columns order.
There are high chances to make mistakes in the query optimization area and missing the good indexes if the query optimized done by nonprofessional. Generally, It supposed to be done by a DB performance expert or DB architect.
Query Optimization Tools:
There are MySQL Query Optimization tools available in the market since 2017. SmartWorkbench is one of my favorite tools having many advanced features such as Query Optimization, Slow query log analyzing, SQL code generator with Dynamic Query Builder and MySQL production issues Debugger. I recommend you to use SmartWorkbench query performance tools if your project doesn’t have a DB performance expert/ DB Architect. Also, it helps to cross verify if you are learning MySQL with the tool until you become an expert.
Query Optimization using SmartWorkbech tool
It is easy to identify such problems using SmartWorkbnech. It can trigger a warning after you have developed the SQL query ends with a semicolon(;). The tool automatically analyzes the query and gives a warning if the query doesn’t have an optimal index or if it goes for a full table scan in the MySQL optimizer Query execution plan.
If you click that warning icon(1) or Query Optimizer icon (2) Then it will open the Query Optimizer module.
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, percentage of query execution time improvement for each state of query execution. It is very useful to identify significant performance improvement before and after Query optimization. It has given more 100X performance most of my SQL Queries.
Bulk Queries Optimization:
How to log all slow queries in MySQL?
Generally, MySQL Can save all slow queries into a file after enabling MySQL slow query log file. By default, it is disabled and easy to enable without restart MySQL using the following MySQL commands.
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1;These MySQL configuration settings help to save all SQL Queries which has taken more than 100 milliseconds. However, The MySQL variable long_query_time can control the time interval, you can change it if you want to increase or decrease. As for my knowledge, MySQL gives the best performance after optimizing all slow queries(>100ms) which have taken more than 100ms execution time.
After one day, the slow query log file has all queries which need query optimization. MySQL creates the slow query log file in the data directory folder. Using the following command you get the exact location of the slow query log file.
show VARIABLES like '%slow_query_log_file%'MySQL Slow Query Log Analyzer:
In my view, The SmartWorkbench has many advanced features and one of them is the Slow query log analyzer(1). It is a MySQL GUI tool that can connect to MySQL and pull a slow query log file(4) directly from OS if OS credits saved with the tool. Also, It allows you to drag and drop(2) or upload slow query log files(3).
After that, It shows a consolidated report after submitting slow query log files with all keen observations including high frequency executed Queries, slow response queries, Queries execution time, lock time, total scanned records, total returned records, and Query ratting. Another key point, It helps you to easily identify problematic queries which are having good indexes.
Most Importantly, It can do Query optimization for all slow queries and provide optimal indexes for all of them at once. There is an option to download the report in excel format. You can observe significant overall DB performance after adding them. As a result, It improves your application response time.
Selected Query Optimization using SmartWorkbench:
Also, It allows analyzing and optimizing selected queries if you double-click on the Query Rating / Explain columns in the report.
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.
Explain displays the Query execution plan in the table and visual diagram modes which is very useful for Query execution plan analysis.
Database Design:
Database design nothing but follow normalization rules and avoid data redundancy. Primarily focused on heavyweight datatype columns in all tables. The following all datatype are heavyweight datatype, and we should not use them if we save reduce data on the columns.
Note: In some cases, you can consider CHAR and VARCHAR datatype or heavy if the size is greater than 50 characters.
It needs to create a new table with all unique values with the primary key and build foreign key relations. I would be helpful If you following Normalization forms.
MySQL Configuration Variables Optimization:
There are many tools & scripts are available which can analyze live traffic and recommend good settings. These are the list of tools available
As a result of all these implementations, you can observe more than 100% overall DB performance. I suggest conducting a lead test to find exact performance improvements and add results in the below comments.
In conclusion, MySQL server optimization can do by the application team with help to the latest tool for getting the best DB performance.
