Posted on

How to Tune SQL Statement with LCASE function on index field?

Some business requirements may need to compare the lower case of an indexed column to a given string as a data retrieval criterion.

Here is an example SQL that retrieves records from the EMPLOYEE table employee if the lower case of the name is equal to the string ‘richard’.

select  *
  from employee
 where LCASE(emp_name)=‘richard’

Here the following are the query plans of this SQL, it takes 17 seconds to finish. The query shows a “Full Table Scan Employee”  

You can see that this SQL cannot utilize index scan even if the emp_name is an indexed field. Let me add a “Force Index(emp_name_inx)“hint to the SQL and hope it can help MySQL SQL optimizer to use index scan, but it fails to enable the index scan anyway, so I add one more dummy condition “emp_name >= ””, it is an always true condition that emp_name should be greater or equal to a smallest empty character, it is used to increase the cost of not using emp_name_inx index. There is another condition added “emp_name is null” to correct this condition if emp_name is a null value.

select  *
from   employee force index(EMPS_NAME_INX)
where  LCASE(emp_name) = ‘richard’
     and ( emp_name >= ”
        or emp_name is null )

Here is the query plan of the rewritten SQL and it is running much faster. The new query plan shows that an Index Scan is used now and takes 2.79 seconds only.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 6 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

Posted on

How to use FORCE INDEX Hints to tune an UPDATE SQL statement?

improve performance of sql query

We used to use FORCE INDEX hints to enable an index search for a SQL statement if a specific index is not used. It is due to the database SQL optimizer thinking that not using the specific index will perform better.  But enabling an index is not as simple as just adding an index search in the query plan, it may entirely change the structure of the query plan, which means that forecasting the performance of the new Force Index hints is not easy. Here is an example to show you how to use FORCE INDEX optimization hints to tune a SQL statement.

A simple example SQL that updates EMP_SUBSIDIARY if the emp_id is found in EMPLOYEE with certain criteria.

update EMP_SUBSIDIARY set emp_name=concat(emp_name,'(Headquarter)’)
where emp_id in
(SELECT emp_id
  FROM EMPLOYEE
 WHERE  emp_salary <1000000
   and emp_grade<1150)

Here the following is the query plan of this SQL, it takes 18.38 seconds. The query shows a Full Table Scan of EMPLOYEE and then Nested Loop to EMP_SUBSIDIARY with a Unique Key Lookup of Emp_sub_PK index.

We can see that the filter condition “emp_salary <1000000 and emp_grade<1150” is used for the full table scan of EMPLOYEE. The estimated “filtered (ratio of rows produced per rows examined): 3.79%”, it seems the MySQL SQL optimizer is failed to use an index to scan the EMPLOYEE table. We should consider forcing MySQL to use either one of emp_salary or emp_grade index.

Unless you fully understand the data distribution and do a very precise calculation, otherwise you are not able to tell which index is the best?

Let’s try to force the index of emp_salary first.

update   EMP_SUBSIDIARY
set    emp_name=concat(emp_name,‘(Headquarter)’)
where emp_id in (select  emp_id
         from    EMPLOYEE FORCE INDEX(`emps_salary_inx`)
         where  emp_salary < 1000000
           and emp_grade < 1150)

This SQL takes 8.92 seconds and is 2 times better than the original query plan without force index hints.

Let’s try to force the index of emp_grade again.

update   EMP_SUBSIDIARY
set    emp_name=concat(emp_name,‘(Headquarter)’)
where emp_id in (select  emp_id
         from    EMPLOYEE FORCE INDEX(`emps_grade_inx`)
         where  emp_salary < 1000000
           and emp_grade < 1150)

Here is the result query plan of the Hints FORCE INDEX(`emps_grade_inx`) injected SQL and the execution time is reduced to 3.95 seconds. The new query plan shows an Index Range Scan of EMPLOYEE by EMP_GRADE index, the result is fed to a subquery2(temp table) and Nested Loop to EMP_SUBSIDIARY for the update. This query plan’s estimated cost is lower and performs better than the original SQL. It is due to the limited plan space in the real-time SQL optimization process, so this query plan cannot be generated for the original SQL text, so manual hints injection is necessary for this SQL statement to help MySQL database SQL optimizer to find a better query plan.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the Hints injected SQL is more than 4.6 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

Posted on

How to Tune SQL Statements with NO_RANGE_OPTIMIZATION Hints Injection?

There are some SQL statements with performance problem can be tuned by Hints injection only. Here is an example to show you how to use NO_RANGE_OPTIMIZATION optimization hints to tune a SQL statement.

A simple example SQL that retrieves data from EMPLOYEE and EMP_SAL_HIST tables.

select * from employee a,emp_sal_hist h
where  a.emp_id =h.sal_emp_id
and  a.emp_dept < ‘B’
and h.sal_salary  between 1000000 and 2000000

Here the following are the query plans of this SQL, it takes 24.3 seconds. The query shows an Index Range Scan (EMPS_DPT_INX) of EMPLOYEE and then Nested Loop to EMP_SAL_HIST with a Non-Unique Key Lookup of SALS_EMP_INX index.

The EMP_SAL_HIST is the employee’s salary history table which keeps more than one salary record for each employee. So, EMPLOYEE to EMP_SAL_HIST is a one-to-many relationship. The speed of a nested loop operation is highly dependent on the driving path of two nested loop tables. MySQL SQL optimizer estimated that the condition (a.emp_dept < ‘B’) can rapidly reduce the result set, so the driving path that “from EMPLOYEE to EMP_SAL_HIST” is selected.

Unless you fully understand the data distribution and do a very precise calculation, otherwise you are not able to tell whether this driving path is the best or not.

How to make MySQL consider another driving path “from EMP_SAL_HIST to EMPLOYEE”? Let’s take a look at MySQL documentation:

NO_RANGE_OPTIMIZATION: Disable index range access for the specified table or indexes. This hint also disables Index Merge and Loose Index Scan for the table or indexes. By default, range access is a candidate optimization strategy, so there is no hint for enabling it.

This hint may be useful when the number of ranges may be high and range optimization would require many resources.

To disable the Index Range Scan of the EMPLOYEE table, I explicitly add a Hints /*+ QB_NAME(QB1) NO_RANGE_OPTIMIZATION(`a`@QB1) */  to the SQL statement and hope that MySQL will use the Index Range Scan by the condition (h.sal_salary between 1000000 and 2000000) as the first driving table.

select  /*+ QB_NAME(QB1) NO_RANGE_OPTIMIZATION(`a`@QB1) */ *
from    employee a,
     emp_sal_hist h
where a.emp_id = h.sal_emp_id
     and a.emp_dept < ‘B’
     and h.sal_salary between 1000000 and 2000000

Here is the result query plan of the Hints injected SQL and the execution time is reduced to 10.01 seconds. The new query plan shows that the driving path is changed from EMP_SAL_HIST table nested loop to EMPLOYEE table. So, sometimes you may make use of the NO_RANGE_OPTIMIZATION hint to control the driving path order to see if MySQL can run your SQL faster.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the Hints injected SQL is more than 2 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

Posted on

Importance of Backup & Recovery in MySQL Database and SQL

MySQL database and SQL

A database is the cornerstone of any application. For this reason, maintaining one or more backup and recovery options remains a priority for every database professional. There are multiple alternatives you can choose from as per the specific needs of your organization’s database.

In this post, we will examine some of the most popular back-ups and restore strategies for MySQL database and SQL. We will also touch upon the reasons why databases require backups on a regular basis.

Why Do We Need Backups for MySQL Database?

As a DBA, you’ll need backup and recovery to support data in multiple cases, such as:

Discrepancies in Data: Users may accidentally delete or update incorrect data in the primary or replica node.

Data Centre Failure: An indefinite power outage or internet connectivity issue can spell trouble for your organization.

Disk Damage: If the disk is stalling for too long due to some kind of damage, it can greatly reduce performance. In cloud services for Oracle database, it translates into a broken DB instance that cuts access.

Broken Data: In case of a power outage, MySQL may fails to write data and close files as usual. There are also instances where MySQL fails to restart and doesn’t work despite the crash recovery process because of corruption in data.

Legislation/Regulation: Backups and recovery options ensure business value and client satisfaction.

Various Kinds of Backups for MySQL Database

Given below are some common backup categories that suit a range of needs:

Physical: These comprise the exact copies of database files and may contain part or all of the MySQL directory. The most common use of this type of backup is to make a new replica node and respond to host failure in a convenient manner. Experts recommend restoring data with the help of the same MySQL database version.

Offsite: This is one of the most recommended backup alternatives as it guarantees an untouched copy in case of data centre or host failure. It involves copying the data to the cloud, an external file server or another external source. However, sometimes it may take longer to download the files from the cloud or server than the recovery process. Therefore, experienced database professionals keep about a week of data locally on a backup server for quick recovery.

Logical: It is useful for smaller quantities of data as it is slower in comparison to physical backup methods. It essentially consists of dumps from INSERT and CREATE statements. It is useful in addressing data corruption or when you must recover a subset of tables. Although the output is greater in logical backups, especially when that data is present in text format, you can perform a quick compression if the software you’re using requires it. For instance, you can use Mydumper and mysqldump to compress and redirect the data to the zip folder.

Incremental: This type of backup contains all the changes made in the organization’s Oracle database and SQL server since the last backup. It is, therefore, quite useful for enormous datasets since it allows you to take small backups (experts recommend this after you’ve taken a full backup) as data comes later.

Differential: It consists of copying the modifications since your previous backup. One advantage of a differential backup is that it saves disk space. This is because the data in these backups mostly remains the same, so the result leads to backups that are substantially smaller in size.

Posted on

How to Tune SQL Statements with Rewrite and Hints Injection for MySQL?

sql tuning for MySQL

There are some SQL statements with performance problem have to be tuned by SQL syntax rewrite and Hints injection, it is a little bit difficult for SQL tuning newcomers to master this technique. Developers not only have to understand the relationship between SQL syntax and the final query plan generation but have to understand the usage of optimizer hints and its limitations. Sometimes these two tuning techniques application will affect each other in a complex SQL statement.

Here is a simple example SQL that retrieves data from EMPLOYEE and DEPARTMENT tables.

select  * from employee,department
where emp_dept=dpt_id
   and emp_dept<‘L’
   and emp_id<1500000
   and emp_salary= dpt_avg_salary
 order by dpt_avg_salary

Here the following are the query plans of this SQL, it takes 7.7 seconds to finish. The query shows a “Full Table Scan Department” and nested loop Employee table with a Non-Unique Key Lookup EMPS_SALARY_INX.

You can see that this SQL cannot utilize index scan even though the dpt_dept is an indexed field. It is because the condition emp_dept<‘L’ is not explicitly induced the condition dpt_id < ‘L’ although emp_dept=dpt_id is also listed in the where clause.

To enable the index search of Department table, I explicitly add a condition dpt_id < ‘L’ to the SQL statement as the following:

select   *
from  employee,
     department
where  emp_dept = dpt_id
     and dpt_id < ‘L’
     and emp_dept < ‘L’
     and emp_id < 1500000
     and emp_salary = dpt_avg_salary
order by  dpt_avg_salary

Here is the query plan of the rewritten SQL and the execution time is reduced to 3.4 seconds. The new query plan shows that an Index Range Scan is used for the Department table and nested loop Employee table.

You may find that the nested loop to Employee by EMPS_SALARY_INX lookup may result into a lot of random access to the Employee table. Let me add a BKA hint to ask MySQL to use ‘Batched Key Access’ to join the two tables.

select   /*+ QB_NAME(QB1) BKA(`employee`@QB1) */ *
from  employee,
     department
where  emp_dept = dpt_id
     and dpt_id < ‘L’
     and emp_dept < ‘L’
     and emp_id < 1500000
     and emp_salary = dpt_avg_salary
order by  dpt_avg_salary

The new query plan shows a Batched Key Access is used to join Department and Employee tables, you can BAK information from MySQL manual for details, the new plan takes only 1.99 seconds and it is more than 3 times better than the original SQL syntax.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 3 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

Posted on

How to Tune SQL Statements to Run SLOWER… but Make Users Feel BETTER (MySQL)?

MySQL database and SQL

Your end-users may keep on complaining about some functions of their database application are running slow, but you may found that those SQL statements are already reached their maximum speed in the current MySQL and hardware configuration. There may be no way to improve the SQL unless you are willing to upgrade your hardware. To make your users feel better, sometimes, you don’t have to tune your SQL to run faster but to tune your SQL to run slower for certain application’s SQL statements.

This is an example SQL that is used to display the information from tables Emp_subsidiary and Employee if they are satisfied with certain criteria. This SQL is executed as an online query and users have to wait for at least 5 seconds before any data will be shown on screen after the mouse click.

select  *
from    employee a,
         emp_subsidiary b
where   a.emp_id = b.emp_id
         and a.emp_grade < 1050
         and b.emp_salary < 5000000
order by a.emp_id

Here the following is the query plan and execution statistics of the SQL, it takes 5.48seconds to extract all 3645 records and the first records return time ”Response Time(Duration)” is 5.39 seconds. The query shows a “Full Table Scan b (emp_subsidiary)” to Nested-Loop “a (employee)” table, an ORDER operation is followed by sorting the returned data by emp_id. You can see there is a Sort Cost=7861.86 at the ORDER step on the query plan. It is the reason that users have to wait at least 5 seconds before they can see anything shows on the screen.

To reduce the sorting time of a.emp_id, since a.emp_id=b.emp_id, so I can rewrite the order by clause from “order by a.emp_id” to “order by b.emp_id”, MySQL now can eliminate the sorting time by using the EMPLOYEE_PK after the nested loop operation.

select  *
from    employee a,
         emp_subsidiary b
where   a.emp_id = b.emp_id
         and a.emp_grade < 1050
         and b.emp_salary < 5000000
order by b.emp_id

Although the overall Elapsed Time is higher in the new query plan, you can see that the response time is reduced from 5.397 seconds to 0.068, so the users can see the first page of information on the screen instantly and they don’t care whether there are 2 more seconds for all 3,645 records to be returned. That is why SQL tuning is an art rather than science when you are going to manage your users’ expectations.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

Posted on

How to Tune SQL Statements with CONCAT Operator for MySQL?

oracle sql performance tuning

There may be some business requirements that need to compare concatenate strings and column with a given unknown length of the bind variable. Here is an example SQL that retrieves data from EMPLOYEE and DEPARTMENT tables where employee’s department ID must concatenate two strings before it is compared to an unknown length of variable @dpt_var

select * from employee,department
where concat(concat(‘A’,emp_dept),‘B’) = @dpt_var
and  emp_dept= dpt_id

Here the following are the query plans of this SQL, it takes 23.8 seconds to finish. The query shows a “Full Table Scan Employee” to nested loop Department table.

You can see that this SQL cannot utilize index scan even the emp_dept is an indexed field. Let me add a “force index(EMPS_DPT_INX) hints to the SQL and hope it can help MySQL SQL optimizer to use index scan, but it fails to enable the index scan anyway, so I add one more dummy condition emp_dept >= ” , it is an always true condition that emp_dept should be greater or equal to a smallest empty character. It is to fool MySQL SQL optimizer that emp_dept’s index is a reasonable step.

select  *
from  employee force index(EMPS_DPT_INX),
     department
where  concat(concat(‘A’,emp_dept),‘B’) = @dpt_var
     and emp_dept >= ”
     and emp_dept = dpt_id

Here is the query plan of the rewritten SQL and it is running faster. The new query plan shows that an Index Range Scan is used for Employee table first and then nested loop Department table.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 3 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/
Posted on

MySQL SQL Performance Tuning: 8 Great Monitoring Practices

MySQL SQL performance tuning

DBAs don’t mind getting the attention of the management, as long as it’s for a positive reason. I mean once in a while, but not all the time; many would still prefer to remain off-radar, quietly ensuring the organization’s databases stay running without a hitch.

Since they have significant accountability in an organization, it is essential for them to beware of all the things that could go south. They can do this by following certain excellent practices for monitoring and MySQL SQL performance tuning, which we will explain in this post.

Best MySQL Database and SQL Monitoring Practices

With these practices, you can avoid being recognized by management for the database outage that took four days to fix, instead of your people skills.

  1. Conduct Regular Health Checks of Your Database

A database administrator knows how important it is to schedule regular health checks for their database. Note that every database has its own maintenance requirements, and that the health checks should be geared toward particular functional needs.

Databases that are non-critical do not require as frequent checks as mission-critical or life-critical databases. A local bookshop’s customer rewarding app failing isn’t as severe as the failure of a missile defense system, for instance.

  • Monitor MySQL Availability

This is perhaps the most essential metric to follow, since the unavailability of the database won’t leave much choice, nor will the other metrics matter until this particular issue is resolved. Use the Run dialog to check availability. You need to type in “ -mysqladmin -h 192.168.1.95 -u root -p” to do so, and initiate diagnostics in case there’s a problem.

  • Check for Unsuccessful Connections & Error Logs

Monitoring the list of unsuccessful connections can slowly but surely help you identify both malicious activity and errors that aren’t as serious (caused by human error like incorrect id\password or misapplied permissions), in the application.

You are likely to get a broader picture in this manner, which will enable you to recognize larger recurring problems so that you can address them appropriately. You can keep track of failed connections by running the following command –

SHOW GLOBAL STATUS LIKE ‘aborted_connects’;

– to know the number of aborted connection attempts on the database in a provided time range.

  • Identify Deadlocks in InnoDB

In MySQL database and SQL, a deadlock takes place when multiple transactions put a lock on a resource required by another transaction. Deadlocks lead to retarded processing, increased timeouts – and unhappy users. Using the query “SHOW ENGINE INNODB STATUS;” will help you find deadlocks and fix them.

  • Observe Configuration Changes

An abrupt decline in performance can be the result of any number of causes. However, checking for any recent configuration changes can help you spot any adversely affecting ones and save you a considerable amount of time.

  • Keep an Eye On the Slow Queries Log

Queries that are slow make the database operate slower as well. This is due to an increase in CPU and memory usage. Assess the Slow Queries log from time to time to know if any queries are taking excessive time to run. You can then proceed towards identifying the root cause and resolving it. 

  • Maintain Visibility to Comprehend the Main Reason Behind Performance Issues

Although regular health checks are important for MySQL SQL performance tuning from the perspective of maintaining high availability, they are not as useful in terms of overall system troubleshooting.

This is because periodic performance concerns may not appear during a routine health check, which is also why a consistent visibility must be established with the MySQL environment.

The quicker you can uncover and fix performance related problems, the fewer the users that will be affected, since downtime will be considerably decreased. 

Setting alerts for critical occurrences can give you the chance to react as quickly as possible, in case a threshold is surpassed. You may set alert thresholds for these typical performance deterioration sources, at least –

  • Substantial deviations from baseline metrics, in terms of performance tuning in SQL MySQL
  • Excessive CPU utilization
  • Query latency
  • Query faults
  • Connection restraints
  • Buffer pool usage
  • Identify and Resolve Performance Issues Quickly

A database monitoring and MySQL SQL performance tuning tool is going to be the best option in nearly every case, so that issues are resolved before they grow into bigger problems.

There are plenty of performance tuning and monitoring tools on the market that come with a broad range of features at varying price ranges. Choosing the right one for your database will depend on your budget and requirements.

As long as you know what to look for in a performance tuning tool, you won’t have many issues in making your selection. Given below are a few features you should consider if you want a tool to perform SQL tuning for MySQL –

  • Scalability
  • Mobile tracking
  • Intuitive User Interface
  • Affordable yet feature-filled
  • Zero connection limitations
  • Different analysis variations, such as
    • Multidimensional workload analysis
    • Alarm source analysis
    • Blocking analysis
  • Smart alarms
  • Historical data monitoring
Posted on

How to Tune SQL statement with Transitive Dependency Improvement for MySQL?

oracle query optimizer too

The following is an example shows a SQL statement with two conditions “emp_dept=dpt_id and emp_dept<‘L’”

select  *  from employee,department
where  emp_dept=dpt_id
  and  emp_dept<‘L’
  and  emp_id<1500000
  and  emp_salary= dpt_avg_salary
order    by  dpt_avg_salary

Here the following is the query plan of this SQL in Tosska proprietary tree format, it takes 8.84 seconds to finish.

The query plan looks reasonable that shows a full table scan of DEPARTMENT to nested-loop EMPLOYEE table, the records in EMPLOYEE table being nested-loop must satisfy with the condition “emp_id<1500000” and the corresponding index EMPS_SALARY_INX is also used. Due to the number of records in the first driving table in a Nested Loop Join is very critical to the join performance, we should find a way to narrow down the number of result records of DEPARTMENT table before it is used to nested-loop EMPLOYEE table.

As the conditions “emp_dept=dpt_id and emp_dept<‘L’”, it implies that “dpt_id < ‘L’” is also true, let me add this extra condition to the SQL, it helps MySQL SQL optimizer to make a better decision with more information provided by the new SQL syntax, this technique is especially useful for MySQL database.
Remark:
Oracle or MS SQL Server are doing very good on their internal Transitive Dependency Improvement in their SQL optimizer already, so this technique may not work for Oracle and MS SQL Server.

select      *
from        employee,
       department
where     emp_dept = dpt_id
    and dpt_id < ‘L’
    and emp_dept < ‘L’
    and emp_id < 1500000
    and emp_salary = dpt_avg_salary
order by dpt_avg_salary

Let’s see the DEPARTMENT is now being filtered by the new condition “dpt_id < ‘L’ “ with an index range scan. You can see the estimated Rows 401 of DEPARTMENT table is now being trimmed down to 176. The rewritten SQL now takes only 3.8 seconds with such a simple change in syntax.

This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that this rewrite is more than 2 times faster than the original SQL with such an easy change in the syntax.
https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

Posted on

How to Tune SQL Statement with Driving Path Control for MySQL?

sql tuning for MySQL

The following is an example shows a SQL statement with two potential table join paths.  “Employee to Department” and “Department to Employee” are potential driving paths which will be decided by MySQL SQL optimizer during SQL optimization stage.  

select  emp_id,emp_name,dpt_avg_salary
  from employee ,department
where emp_dept=dpt_id
and emp_dept like ‘A%’
and dpt_id like ‘A%’;

Here the following is the query plan selected by MySQL SQL optimizer in Tosska proprietary tree format, it takes 59 seconds to finish.

The query plan looks reasonable that uses DEPARTMENT’s Primary Key (DPT_ID) to fetch DEPARTMENT table first and then nested loop EMPLOYEE table by EMP_DEPT index, the speed of this query plan depends on the size of EMPLOYEE table and the records distribution according to the EMP_DEPT code.

If we want to change the driving path of the query plan from EMPLOYEE to DEPARTMENT, let me add a ifnull(dpt_id,dpt_id) dummy function to disable the DPT_ID index search, so it can artificially add cost to condition search DEPARTMENT table first. It means that using EMP_DEPT index search cost is relative cheaper now, so EMPLOYEE to DEPARTMENT driving path is probably be selected by MySQL SQL optimizer in the following:

select  emp_id,
        emp_name,
        dpt_avg_salary
from     employee,
        department
where  emp_dept = dpt_id
        and emp_dept like ‘A%’
        and ifnull(dpt_id,dpt_id) like ‘A%’

EMPLOYEE to DEPARTMENT driving path plan is generated by MySQL now and it takes only 18.8 seconds only to finish the query.

If we know that using the EMP_DEPT index is not that efficient due to the selectivity of “ like ‘A%’ “ condition may not high enough to utilize the index range scan. Let me add an additional ifnull(emp_dept,emp_dept) dummy function to disable the EMP_DEPT index range scan too in the following:

select  emp_id,
        emp_name,
        dpt_avg_salary
from     employee,
        department
where  emp_dept = dpt_id
        and ifnull(emp_dept,emp_dept) like ‘A%’
        and ifnull(dpt_id,dpt_id) like ‘A%’

Now, MySQL use full table scan of EMPLOYEE table to nested loop DEPARTMENT table. The speed is further improved to 15 seconds now.

This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the best rewrite is around 4 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/