Databases Reference
In-Depth Information
• MySQL stores the parameters directly into buffers on the server, which eliminates
the need for the server to copy values around in memory.
Prepared statements can also help with security. There is no need to escape or quote
values in the application, which is more convenient and reduces vulnerability to SQL
injection or other attacks. (You should never trust user input, even when you're using
prepared statements.)
You can use the binary protocol
only
with prepared statements. Issuing queries through
the normal
mysql_query()
API function will
not
use the binary protocol. Many client
libraries let you “prepare” statements with question-mark placeholders and then spec-
ify the values for each execution, but these libraries are often only emulating the
prepare-execute cycle in client-side code and are actually sending each query, as text
with parameters replaced by values, to the server with
mysql_query()
.
Prepared Statement Optimization
MySQL caches partial query execution plans for prepared statements, but some opti-
mizations depend on the actual values that are bound to each parameter and therefore
can't be precomputed and cached. The optimizations can be separated into three types,
based on when they must be performed. The following list applies at the time of this
writing:
At preparation time
The server parses the query text, eliminates negations, and rewrites subqueries.
At first execution
The server simplifies nested joins and converts
OUTER JOINs
to
INNER JOINs
where
possible.
At every execution
The server does the following:
• Prunes partitions
• Eliminates
COUNT()
,
MIN()
, and
MAX()
where possible
• Removes constant subexpressions
• Detects constant tables
• Propagates equalities
• Analyzes and optimizes
ref
,
range
, and
index_merge
access methods
• Optimizes the join order
See
Chapter 6
for more information on these optimizations. Even though some of them
are theoretically possible to do only once, they are still performed as noted above.
