Skip to Main Content
MySQL Stored Procedure Programming
book

MySQL Stored Procedure Programming

by Guy Harrison, Steven Feuerstein
March 2006
Intermediate to advanced content levelIntermediate to advanced
640 pages
17h 8m
English
O'Reilly Media, Inc.
Content preview from MySQL Stored Procedure Programming

Chapter 19. Tuning Stored Programs and Their SQL

This chapter kicks off the set of chapters in this book that are concerned with optimizing the performance of your stored programs. Like any program, a stored program might be correct in all of its functional aspects, but still be considered a failure if it does not perform well. Performance tuning of MySQL stored programs is of particular importance because the stored program language is interpreted, and thus it does not benefit from the performance improvements that can be obtained by optimizing compilers such as the ones common in languages such as C and Java. (Strictly speaking, Java is also an interpreted language, but the Java JVM performs a number of sophisticated optimizations.) Stored programs also almost always involve significant database activity and therefore are quite likely to become a performance bottleneck for the application as a whole.

We believe that there are three main principles of stored program optimization:

Optimize SQL

The SQL inside of a stored program must be optimized if the stored program has any chance of running efficiently. Untuned SQL statements can easily take hundreds or even thousands of times longer to return results than well-tuned SQL statements, so we therefore recommend tuning the SQL inside a stored program before tuning the stored program code itself. We'll look at SQL tuning in detail in the next few chapters.

Break up complex SQL

Sometimes you can use stored programs to break up complex and ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

Jesper Wisborg Krogh
MySQL 8 Administrator???s Guide

MySQL 8 Administrator???s Guide

Chintan Mehta, Hetal Oza, Subhash Shah, Ravi Shah
MySQL Cookbook, 4th Edition

MySQL Cookbook, 4th Edition

Sveta Smirnova, Alkin Tezuysal
Learning MySQL, 2nd Edition

Learning MySQL, 2nd Edition

Vinicius M. Grippa, Sergey Kuzmichev

Publisher Resources

ISBN: 0596100892Supplemental ContentErrata Page