MySQL query Optimization Basics

In case, if your MySQL server has a problem with high CPU /RAM/Disk IO usage or long-running queries/query locking issues? The most compelling evidence for these issues with bad SQL queries which not properly optimized. Your Query will be scanned all rows if you did not create the best index and it is dead slow because of scans all rows in the table called Full Table Scan. MySQL Query Optimization is a key area to improve MySQL DB performance more than 100x. However, in this blog I will explain MySQL Optimization basics, common mistakes, available tools.

What is a Full Table Scan?

In case, If your query scan all records is nothing but full table scan. Usually, It happens if the table/s don’t have an optimal index for your query.

MySQL optimization After indexing on the id column.

Is MySQL Query Optimization simple?

In many cases, MySQL Optimizer uses only one index per table for filter records in most of the cases. In fact, there will be N!*(N-1) +N no.of indexes combinations that are available for the query. Here N is no.of unique columns in WHERE, GROUP BY and ORDER BY clauses. All columns belong to the same table and N >=1.

• 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.

There are high chances to make mistakes in the query optimization area and missing the optimal indexes if the query optimized done by nonprofessional. Generally, It supposed to be done by a DB performance expert or DB architect.

We must select columns for index based on index type and index’s behavior. There is only one index per table per query which can scan fewer records to return the result. In many cases, It will not scan more than 120% of return records. It is nothing but an Optimal index. In fact, there is very little probability to select the optimal index using shortcut /easy methods by nonprofessionals.

What is the Optimal index?

There will be N+N!*(N-1) indexes combination possible for the query, Here N is the total number of columns in WHERE GROUP and ORDER By clause. There will be only one best index scans less no.of records for getting the final result set and it is nothing but optimal index.

Index Common Mistakes

Index design job supposes to have done by DB performance engineer or DB architect but I have observed in many start-ups, small, medium and even some big companies indexes are not designed by DB Performance engineers, and they choose random columns for creating indexes and it causes many DB performance issues. The peoples who are creating indexes should be expert level knowledge about all indexes archers, MySQL optimizer behavior and how they work with query WHERE conditions, Group by and ORDER BY column for reducing scans and avoid creating temp tables, file store

1. Create indexes on all columns in the WHERE Clause with the same order.
2. Creating indexes on each column in WHERE Clause filters.
3. Create an index on Randomly selected columns
4. Creating individual indexes on all columns in the table
5. Create an index with all columns in the table.

What are the problems with wrong creating indexes?

If your DB indexes not designed by experts then there are fewer chances to select optimal indexes that take more time to execute on DB. Some times they may work better than Table Full table scan, but they will create the following production issues gradually when tables have been grown big size

1. Dead-Lock
2. Query locking
3. Long-running Queries
4. Long-running Transactions
5. Replication lagging
6. High CPU utilization
7. High RAM utilization
8. High DISK IO utilization
9. Too many DB connections
10. Storage and space issues
11. Table fragmentation issues

If your DB server is facing anyone these issues means your DB indexes are not designed in the right approach.

How to find out Optimal MySQL indexes?

It is good to hire DB performance engineer/DB Architect if your project doesn’t have. In case if you are having a budget limitation or DB performance engineers shortage then there are two options

1. Use MySQL Query Optimization tools which are available in the market
2. Start learning. I will explain the basics of MySQL Query Optimization.

It is not so simple to become an expert in one day most importantly need real-time experience. Also, it is not good practice to do experiments on the production DB server. I recommend using the MySQL Query Optimization tool until you become an expert or you hired expert.

MySQL Query Optimization Tools

There are two tools available for MySQL Query Optimization in the market.

1. EverSQL
2. SmartWorkbench

Eversql:

If fact, It is a web-based SaaS application that needs to upload tables structures, schema, SQL Queries and/or SQL slow Query logs files.In my view, it is not safe to share project scheam to other companies. After submitting your schema, It generates indexes recommendations and Query recommendations. It is not free and their plan starting from $25 for just 10 queries optimization per month’s validity.

Advantages of EverSQL:

• Simple and Easy and no need any installations.
• Customer Support
• Work with MySQL slow log queries.

Disadvantages of EverSQL:

• It is not free and paid plans are expensive.
• It is not secure to upload your project database schema and SQL statements
• Fewer Efficiencies. Index recommends based on empty tables and may not right all times because columns cardinality is one of the characteristics while choosing columns in an index.
• Not providing query performance improvement reports before and after implementing their recommendations.
• Showing old MySQL query recommendations (Ex Still convert OR logical operator into tow queries UNION. It is very old commendation and the latest version of MySQL(5.6+) MySQL optimizer can do this job, and we don’t need to do it)
• Not designed by MySQL performance experts.

SmartWrokbench:

They are providing desktop applications and web-based service and I recommend to use desktop applications for security because you don’t need to upload your schema or data. It can give detailed reports with indexes recommendations and Query recommendations, MySQL optimizer has rewritten queries. It is free for Educational and non-commercial projects.

Advantages of SmartWorkbench:

• It is free for educational and non-commercial projects.
• Also, It is a MySQL GUI tool and it can recognize when you develop new query and recommend
• Designed by industry-leading DB experts
• More accurate than EverSQL because it checks columns cardinality.
• It integrated with MySQL Slow Query Logs
• Integrated to MySQL Performance Schema Query Statements & Process list
• Integrated MySQL production issues like long-running queries, high CPU
• Good customer care support & Hot Bug fix within 2 weeks’ time.
• It is very easy and comfortable. Once you connect your DB and OS then it can pull slow queries. Reduce tons of manual work.
• It doesn’t need MySQL knowledge.
• It can fix MySQL production issues
• Using MySQL dynamic Query builder you can write SQL queries 10X faster with simple drag and drops.
• Display duplication and unused indexes.
• It can optimize index storage space.
• It works all MySQL versions with the latest features and all types of indexes.

Disadvantages of Smartworkbench:

• Some times it is hanging in macOS.
• Query Optimization is part of features in this tool, and they may concentrate MySQL Debugger which is fixing MySQL production issues like Deadlock, long-running queries, Long transactions, Replication lagging, high CPU, high RAM, high disk IO issues.
• Paid version starting from $15 per month and an unlimited plan for $149 per project for a person.

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

https://www.youtube.com/watch?v=YzL8sHN4M-Q&feature=emb_logo

MySQL Optimization Basics for Query Tuning

In fact, It is always better to learn MySQL Query Optimization. Further, need to learn MySQL indexes basics, Index types of indexes, index behavior and how MySQL uses indexes concepts. What type of filters columns are favor to indexes then we should keep and others we should ignore while selecting columns for index. The following are general rules select or ignore columns for indexes based on index behavior.

• WHERE condition filter columns cardinality[How many distinct values in the column]: WE have to choose high cardinality columns in the index.
• WHERE condition Filter condition types[Equality condition(i.e age=30) & range condition(i.e age>30) ]: we should not take more than on range condition column and it should be rightmost column in the index. We should use B-tree, full-text indexes, hash index and R-tree indexes based on filter types. Also, the index can’t use if the filter columns are in functions.
• Logical Operators between WHERE condition Filter [i.e a=10 OR b=10 / a=10 AND b=10 ]: Indexes can’t eliminate records if filter conditions with OR logical operation, and they are not favorable. We should avoid OR condition filters if there are a combination of AND & OR filters in WHERE clause.
• WHERE condition Filter columns datatype & size. Indexes also need the same amount of storage and it gives good performance for lightweight data types. Also, there is an option to select the substring part of the string datatype for equality filters.
• No. of Tables used in the query: If the query has an N table then it can use N indexes in most cases. It uses only one index for filter records and the rest of the indexes are used to join tables.
• Type of JOINs: LEFT OUTER and RIGHT OUTER JOINS are expensive compare to Equal JOINS
• GROUP BY clause column & Grouping Functions Index can help in avoiding creating TEMP tables
• ORDER BY clauses columns can help for avoiding creating FILE sort
• Table Partition column should be in filters and index for selecting the right partition
• We should select only one range filter cardinality or group by columns or order by columns based on table column and it should be a rightmost column in the index.
• If there are only tow filters with a logical operator OR then need to select individual indexes.
• Ignore filter condition which filters are using LIKE operator with starting wild characters.

Examples:

In the following example, MySQL SELECT query and if you select any one of the indexes can give better than full table scan but only one index can cover all filters and reduce the total no. of rows scan and it is called optimal index. Indexes design is very important and gives the best performance if we use the right way otherwise these will cause production problems.

The Query has two filters with an equality filter and need to check cardinality of them and put a high cardinality column in the first place and another one in the next place in the index. Hear Name column has high cardinality then we should keep Name in the first place and DEPT in the next position. So, If you follow the above rules you need to keep right most column should for range filter. Hear SAL > 200 is a range filter then we need to keep the SAL column is the rightmost column. In summary, The following index is optimal index for the query.

Alter table Employee add index idx_Name_dept_Sal(Name,Dept,Sal)

MySQL explain Plain

Generally, MySQL EXPLAIN command can give MySQL optimizer execution plan details after creating indexes. It can help to show estimated no.of rows need to scan, also, all other possible indexes for the query.

Is the MySQL Explain plan help to select an optimal index?

In fact, It does not help with selecting an optimal index, and we can use it to check execution plans with existing indexes.

MySQL query optimization is simple if your query has only one WHERE condition filter. You can create an index on the column and MySQL optimization query can improve good performance.

Select * from Employee where Fname='John';

You can create an index on Fname Column

Alter table Employee add index idx_Fname(Fname);

MySQL Optimization becomes complexity based Query’s no.of columns in WHERE condition column, filter condition types(range, equal filter), Logical Operators between filter, Filter condition column cardinality, no. of Tables used in the query, Group BY clause column, having ORDER BY clauses and table partitions. Generally, It can use only one index per table. Then, Creating individual indexes are not best practice.

When individual indexes used by MySQL Optimizer?

Creating individual indexes can help when the filter conditions do not favor B tree indexes and optimizer can use two indexes using index merge especially if you where condition filters are 2 or 3 and all of them separated with OR logical operator.

MySQL query Optimizer with OR logical Operator with Example

Select * from Employee where email='john.admon@gmail.com' or FName ='JOIN'; 

Best index indexes for this query

Alter table Employee add index idx_email(email), add index idx_Fname(Fname)

Especially, The MySQL latest version optimizer can convert this query UNION and remove OR logical operator.

Select * from Employee where email='john.admon@gmail.com' 
union
Select * from Employee where FName ='JOIN'; 

What is Covering indexes or Composite indexes in MySQL?

In this case, MySQL Optimizer can use only one index to find out records for the Query. In fact, The covering index can use all possible columns in where condition, Group By and Order By clause. As a result, it scans fewer records to get records for your Query. If you create individual indexes for each column in the where clause then it is better than full scan but it is not the best index in many cases.

One filter condition with equal operator

If your Select Query has only one filter condition in the where clause then we can select index on the where condition: Example Queries

select * from Employee where email='john.admon@gmail.com';
select * from Department where DeptName='Marketing';

In this case, we can create an index on where condition column email

Alter table employee add index idx_email(email);
Alter table Department add index idx_deptName(DeptName);

We can choose a B+ tree index on where clause equality condition filter column.

Single Table Select Query With Multiple Equality Filters separated with AND logical operator

For example, If the query has more than one equality condition filter in the where clause then we need to create a composite index (cover index) based on column cardinality. Moreover, keep a high cardinality column in the leftmost column.

Example Queries:

Select * from Employee where email='john.admon@gmail.com' and firstName='John';
select * from Department where DeptName='Marketing' and Location='LA'

Alter table employee add index idx_email(email,firstName);
Alter table Department add index idx_DeptName(Location,DeptName);

How many columns we can keep in covering indexes for best practice?

Suppose if the select query has more than 10 columns in where clause equality condition filters then, does it need to create indexes on all columns?

In reality, It doesn’t need to create an index on all columns.So, It is always better to select 3 to 4 columns to create a composite index based on columns’ cardinality. Because high cardinality columns can filter maximum records.

For example, if you create an index on all columns as a result, it will take huge storage. Some times, more than date size. Also, it will be slow down INSERT, UPDATE and DELETE commands performance. Also, we can select only the first 32 characters String columns in the top 3 or 4 high cardinality columns while creating cardinality

Index behavior with OR operator

In fact, Index is not able to filter records if there is OR logical operator. If your Query has only two filters separated by OR then we need to create individual indexes on both columns. Generally, MySQL optimizer does index merge to get the result set in the latest versions.

Example SQL Query

Select * from Employee where email='john.admon@gmail.com' or firstName='John';
Welect * from Department where DeptName='Marketing' or Location='LA'

Recommended Index for MySQL Optimizer

Alter table employee add index idx_email(email), add index idx_firstName(firstName);
Alter table Department add index idx_Location(Location),add index idx_DeptName(DeptName);

Notes:

• In most cases, MySQL Optimizer can use only one index for one table in the select query.
• Creating MySQL optimal indexes helps to avoid deadlock, query Locking issues, Long-running Queries, and replication lagging issues in MySQL
• Creating an index on huge size tables are very expensive, it is highly recommended creating an index at database design with the help of DB performance engineers or use SmartWrokbech tool