Why SQL Tuning Is So Important

It might be surprising to you that a book dedicated to stored programming has such extensive coverage of SQL tuning . The reason for this is simple: we regard SQL tuning as an essential skill for anyone writing MySQL stored programs. The simple fact is this:

The vast majority of your stored program execution time is going to be spent executing SQL statements.

Furthermore, poorly tuned (or untuned) SQL can result in programs that are slower by orders of magnitude (e.g., thousands of times slower). Finally, untuned SQL almost never scales well as data volumes increase, so even if your program seems to run in a reasonable amount of time today, ignoring SQL statement tuning now can result in major problems later.

19.1.1. An Instructive Example

The following example demonstrates just how critical the role of SQL tuning is in overall system performance. An application executes a query (which might even be implemented within a stored program) that involves a simple join between two tables, as shown here:

 SELECT sum(sale_value)
 FROM ta_10000 a,tb_10000 b
 WHERE a.sales_id=b.sales_id;

The tables grow in size with each day'accumulation s of data. Initial performance is satisfactory, but within a few days performance is questionable, and within a week the application is virtually unusable. You are called in to examine the situation. When you examine the relationship between table size and elapsed time, you discover the relationship shown in Figure 19-1.

Not only is the performance of the query growing worse as the tables grow, but the rate of increase is itself accelerating. Extrapolating the performance trend, you predict that by the time the tables reach their estimated peak sizes of 1,000,000 rows each, the join will take more than 20 hours to complete!

Figure 19-1. Response time and table row countsbefore tuning

After examining the SQL statements involved in the application, the problemand the solutionseems obvious. An index is needed to support the join, and you can create one with the following statement:

 CREATE INDEX i_tb_1000 ON tb_1000 (sales_id)

Once the index is created, the performance trend adopts the profile shown in Figure 19-2.

Figure 19-2. Table row counts versus elapsed timeafter tuning

The performance improvement is remarkablethe elapsed time for the query has been reduced by more than 99%, and the SQL is more than 100 times faster. Furthermore, the SQL will now scale appropriately as the volumes of data in the tables increase.

No amount of server tuning, stored program tuning, or hardware upgrades could have obtained this improvement. Any such efforts would also have been ultimately futile, because the exponential degradation would eventually overwhelm any performance improvements gained by other measures. For these reasons, SQL tuning should always be performed before attempting any other optimization.

SQL tuning is the most important aspect of overall MySQL tuning. Ensure that SQL is tuned before starting any other optimization exercises.