I can't speak to the exact internals of MySQL, but in database practice
one *generally* uses prepared statements for two reasons:
1. Security
2. Speed
If your system is at all exposed to the outside world *and you at all
value your data*, your biggest concern should /absolutely/ be security.
By using prepared statements, you reduce the number of places to
secure by letting the database do your "escaping" for you (e.g. database
developer's know better what to escape than you). It's actually better
than that, but that's a simple explanation.
If you're not exposed to the Internet at large, and speed is your
concern, prepared statements can give a speed improvement, but not
always. The reason for the alleged speed improvement is that generally
one prepares the statement once and then aggregates the cost of
preparation over more than one execution. Juxtapose with reparsing and
executing for every set of arguments, where the plans are largely the
same: the parsing phase is duplicate work.
But preparation is not always a win. Say I have a table of stored
materials. Picking the "perfect" plan is highly dependent on what data
I have, and what data I want. Take this query as an example
PREPARE( SELECT material FROM stock WHERE mass = ? );
Presumably, just having an index on 'mass' will make things faster,
right? That makes an assumption that I have an evenly distributed set
of data. What if 5 billion items in my warehouse are 5kg, and 3 items
are 10kg? If I plug in 5kg, my indexes are useless, and are in fact a
loss to use. If I plug in 10kg, my indexes are a huge gain. Without
knowing before hand what data I'll need, the planner will likely make a
poor decision in favor of the best "general" decision.
To answer what you can expect from planning: YMMV. I have had code
bases improve from 90 minutes to 5 minutes on the same hardware. I have
also had code bases show decreased performance by implementing planning.
As the previous poster said, the only way you'll know what *your* speed
gain/loss will be, is to do it and find out. You may be surprised.
Kevin
At 10:56p -0500 on 15 Jan 2008, Moon's Father wrote:
To know the exact speed improvement ,you have to have a test yourself append
on your hardware mathine.
On Jan 15, 2008 11:39 PM, mos <[EMAIL PROTECTED]> wrote:
At 11:25 AM 1/14/2008, Jay Pipes wrote:
Are you using the PREPARE STATEMENT server-side syntax or an emulated
prepared statement like in PDO?
-jay
Jay,
Currently I'm not using prepared statements at all. Before I switch,
I wanted to know how much of a speed improvement I can expect, if any.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
