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.

Part I: Stored Programming Fundamentals

Introduction to MySQL Stored Programs

MySQL Stored Programming Tutorial

Language Fundamentals

Blocks, Conditional Statements, and Iterative Programming

Using SQL in Stored Programming

Error Handling

Part II: Stored Program Construction

Creating and Maintaining Stored Programs

Transaction Management

MySQL Built-in Functions

Stored Functions


Part III: Using MySQL Stored Programs in Applications

Using MySQL Stored Programs in Applications

Using MySQL Stored Programs with PHP

Using MySQL Stored Programs with Java

Using MySQL Stored Programs with Perl

Using MySQL Stored Programs with Python

Using MySQL Stored Programs with .NET

Part IV: Optimizing Stored Programs

Stored Program Security

Tuning Stored Programs and Their SQL

Basic SQL Tuning

Advanced SQL Tuning

Optimizing Stored Program Code

Best Practices in MySQL Stored Program Development

MySQL Stored Procedure Programming
MySQL Stored Procedure Programming
ISBN: 0596100892
EAN: 2147483647
Year: 2004
Pages: 208

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net