Advanced MySQL Features - High Performance MySQL - page 284

limited ability to raise and catch errors prior to MySQL 5.5, and so on. (We mention
the limitations of various features as we describe them.) In general, MySQL's stored
routine language is nowhere near as capable as T-SQL or PL/SQL.
• It's hard to profile code with stored procedures in MySQL. It's difficult to analyze
the slow query log when it just shows CALL XYZ('A') , because you have to go and
find that procedure and look at the statements inside it. (This is configurable in
Percona Server.)
• It doesn't play well with statement-based binary logging or replication. There are
so many “gotchas” that you probably should not use stored code with statement-
based logging unless you are very knowledgeable and strict about checking it for
potential problems.
That's a long list of drawbacks—what does this all mean in the real world? Here's an
example where we've seen the use of stored code backfire in real life: in one instance,
using them to create an API for the application to access the database. This resulted in
all access to the database—even trivial primary-key row lookups—going through
CALL queries, which reduced performance by about a factor of five.
Ultimately, stored code is a way to hide complexity, which simplifies development but
can be very bad for performance and add a lot of potential hazards with replication and
other server features. When you're thinking about using stored code, you should ask
yourself where you want your business logic to live: in application code, or in the da-
tabase? Both approaches are popular. You just need to be aware that you're placing
logic into the database when you use stored code.
Stored Procedures and Functions
MySQL's architecture and query optimizer place some limits on how you can use stored
routines and how efficient they can be. The following restrictions apply at the time of
this writing:
• The optimizer doesn't use the DETERMINISTIC modifier in stored functions to opti-
mize away multiple calls within a single query.
• The optimizer cannot estimate how much it will cost to execute a stored function.
• Each connection has its own stored procedure execution plan cache. If many con-
nections call the same procedure, they'll waste resources caching the same execu-
tion plan over and over. (If you use connection pooling or persistent connections,
the execution plan cache can have a longer useful life.)
• Stored routines and replication are a tricky combination. You might not want to
replicate the call to the routine. Instead, you might want to replicate the exact
changes made to your dataset. Row-based replication, introduced in MySQL 5.1,
helps alleviate this problem. If binary logging is enabled in MySQL 5.0, the server
will insist that you either define all stored procedures as DETERMINISTIC or enable
the elaborately named server option log_bin_trust_function_creators .