Advanced MySQL Features - High Performance MySQL - page 282

cause InnoDB to check for a corresponding value in the parent. It must also lock the
row in the parent, to ensure it doesn't get deleted before the transaction completes.
This can cause unexpected lock waits and even deadlocks on tables you're not touching
directly. Such problems can be very unintuitive and frustrating to debug.
You can sometimes use triggers instead of foreign keys. Foreign keys tend to outperform
triggers for tasks such as cascading updates, but a foreign key that's just used as a
constraint, as in our status example, can be more efficiently rewritten as a trigger with
an explicit list of allowable values. (You can also just use an ENUM data type.)
Instead of using foreign keys as constraints, it's often a good idea to constrain the values
in the application. Foreign keys can add significant overhead. We don't have any
benchmarks to share, but we have seen many cases where server profiling revealed that
foreign key constraint checks were the performance problem, and removing the foreign
keys improved performance greatly.
Storing Code Inside MySQL
MySQL lets you store code inside the server in the form of triggers, stored procedures,
and stored functions. In MySQL 5.1, you can also store code in periodic jobs called
events . Stored procedures and stored functions are collectively known as “stored
routines.”
All four types of stored code use a special extended SQL language that contains pro-
cedural structures such as loops and conditionals. 6 The biggest difference between the
types of stored code is the context in which they operate—that is, their inputs and
outputs. Stored procedures and stored functions can accept parameters and return
results, but triggers and events do not.
In principle, stored code is a good way to share and reuse code. Giuseppe Maxia and
others have created a library of useful general-purpose stored routines at http://mysql
-sr-lib.sourceforge.net . However, it's hard to reuse stored routines from other database
systems, because most have their own language (the exception is DB2, which has a
fairly similar language based on the same standard). 7
We focus more on the performance implications of stored code than on how to write
it. Guy Harrison and Steven Feuerstein's MySQL Stored Procedure Programming
(O'Reilly) might be useful if you plan to write stored procedures in MySQL.
It's easy to find both advocates and opponents of stored code. Without taking sides,
we'll list some of the pros and cons of using it in MySQL. First, the advantages:
6. The language is a subset of SQL/PSM, the Persistent Stored Modules part of the SQL standard. It is defined
in ISO/IEC 9075-4:2003 (E).
7. There are also some porting utilities, such as the tsql2mysql project ( http://sourceforge.net/projects/
tsql2mysql ) for porting from Microsoft SQL Server.