When it is not possible to eliminate an SQL statement to improve performance, it might be possible to simplify the statement. Consider the following questions:
- Are all columns required?
- Can a table join be removed?
- Is a join or WHERE restriction necessary for additional SQL statements in a given function?
An important requirement of simplification is to capture all SQL statements in order for a given executed function. Using a sampling process will not identify all possible improvements. Here is an example of a query simplification:
mysql> SELECT fid, val, val -> FROM table1 -> WHERE fid = X;
This query returned 350,000 rows of data that was cached by the application server during system startup. For this query, two-thirds of the result set that was passed over the network was redundant. The fid column was passed as a restriction to the SQL statement, and therefore the value was identical for all rows. The val column was also unnecessarily duplicated. The optimal SQL statement is shown here:
mysql> SELECT val -> FROM table1 -> WHERE fid = X;
The following is an example of a table join simplification that requires knowledge of all SQL statements for a given function. During the process, the following SQL statement is executed to retrieve a number of rows:
mysql> SELECT /* Query 1 */ id FROM table1 -> WHERE col1 = X -> AND col2 = Y;
At a later time in the execution of this function, the following statement was executed using the id value from the previous SQL statement:
mysql> SELECT /* Query 2 */ table2.val1, table2.val2, table2.val3 -> FROM table2 INNER JOIN table1 USING (id) -> WHERE table2.id = 9 -> AND table1.col1 = X -> AND table1.col2 = Y -> AND table2.col1 = Z;
This second SQL statement could be simplified to this:
mysql> SELECT /* Query 2 */ val1, val2, val3 -> FROM table2 -> WHERE table2.id = 9 -> AND table2.col1 = Z
As the first query performs the necessary restriction for the id column (that is, col1 = X and col2 = Y), the join and restriction clauses for table1 are redundant. Removing this join condition simplifies the SQL statement and removes a potential problem if only one statement is altered in the future.
The MySQL database supports subqueries. The performance of subqueries may be significantly slower in certain circumstances than using a normal table join. Here is an example:
SELECT id, label FROM code_opts WHERE code_id = (SELECT id FROM codes WHERE typ='CATEGORIES') ORDER BY seq
This SQL statement can simply be rewritten as follows:
SELECT o.id, o.label FROM code_opts o INNER JOIN codes c ON o.code_id = c.id WHERE c.typ='CATEGORIES' ORDER BY o.seq
The change might appear to be subtle; however, this approach for more complex queries can result in improved query performance.
Understanding the Impact of Views
Developers should know the true class of the table that is used for SQL statements. If the object is actually a view, the impact of SQL optimizations can be masked by the complexity of join conditions for the view definition. A common problem with data warehouse (DW) and business intelligence (BI) tools is the creation of views on top of views. For any view definition, additional information may be retrieved that is not ultimately required for an SQL statement. In MySQL, complex queries involving views can be easily improved by using the necessary underlying tables.