Improve MySQL Database Performance: Know Your Unions & Joins

Correcting slow queries is not as complicated when it is altered slightly. A basic example of such changing can be demonstrated in an OR or IN query, where several values are compared using a WHERE clause.

More often than not, the OR clause can result in a scan for a table or index that may not be the most desirable execution plan regarding IO usage or general statement execution rate. This leads to a requirement to improve MySQL database performance, but before that, let’s analyze unions and joins in this blog. 

OR Versus UNION: Understand & Improve Performance of SQL Query 

Several variables play their roles as the query optimizer designs a plan. 

Such variables involve the following - 

• A combination of various hardware specifications

• Instance and database configurations

• Stats 

• The manner in which the statement has been written. 

When we try to improve performance of SQL query, we attempt to change the manner in which the query has been constructed. Although it may appear normal, and despite two queries resulting in fetching precisely the same results, this simple parameter can decide which path the two queries take to achieve those results.  

Those with some experience in SQL Server have noted that OR typically proves somewhat less efficient than UNION. This is because of an increased number of scans when an OR is used, and while it may prove a better path in rare cases, it still means accessing a larger number of records, which takes more time, and ultimately, slows performance. 

Example to Compare UNION and OR

Let us suppose two separate branches of processes, one in which there are around 360 rows, and the other with slightly lesser rows. Each of these combine to perform the CONCAT function to display the respective result sets together. Along with two individual seek operations, there will also be a main lookup so that the desired SELECT list can be retrieved. 

The scan operation does not need this since all the rows are going to be checked anyhow, so that information will be retrieved during the scan instead of after it is complete. This piece of information will come in handy when you try to improve MySQL database performance, even if it has more to do with the required rows and indexes, than with the comparison between OR and UNION. 

Still, the select list plays its own role in determining whether a seek or a scan will take place. 

Inference

The reason why UNION results in a greater number of seeks rather than scans is due to the need to fulfil specific selectivity criteria to substantiate a seek. 

On the other hand, an OR occurs through a sole operation, which means a scan is more likely to be decided as an efficient course, at the time of checking the percentage of selectivity per column. This may or may not need the user to improve performance of SQL query, depending on certain other factors, which will be discussed in a separate post.

As the default purpose of a UNION is to conduct separate executions for every query, the selectivity of every column is not joined, making it more susceptible to becoming a seek. Currently, as the UNION conducts two tasks, both require matching their result sets with the help of a concatenation query, as mentioned above. 

Although it isn’t a taxing operation per se, the OR clause that works quite similar to an IN query still proves to be more useful when systems needing greater concurrency are involved.