You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by "Bas A.Schulte" <bs...@zeelandnet.nl> on 2002/11/24 22:08:13 UTC
MySQL & prepared sql statements?
Hi,
I'm wondering if I would gain (some) performance by preparing my sql
statements once during ChildInit when using MySQL? Does it cache
execution plans for sql statements? I know it makes a big difference on
Oracle but I'm not sure what MySQL is like in this respect.
Regards,
Bas.
Re: MySQL & prepared sql statements?
Posted by Stas Bekman <st...@stason.org>.
David Wheeler wrote:
> On Sunday, November 24, 2002, at 01:08 PM, Bas A.Schulte wrote:
>
>> I'm wondering if I would gain (some) performance by preparing my sql
>> statements once during ChildInit when using MySQL? Does it cache
>> execution plans for sql statements? I know it makes a big difference
>> on Oracle but I'm not sure what MySQL is like in this respect.
>
>
> MySQL doesn't have a prepared statement feature, so you wouldn't get the
> same kind of performance gain that you would with Oracle. That said,
> DBD::mysql does a bit of munging with your statements, and if you use
> prepare(), it does that munging only once per statement.
>
> The upshot: Use prepare() whenever possible to maximize performance.
> Even better, used prepare_cached() for statements you'll be using a lot,
> and then they'll be prepared only the first time prepare() is called
> (per Apache child). Doing so will also make it easier to gain the
> performance benefit if you later decide to switch to a database such as
> Oracle that supports prepared statements in the database.
What David said, plus your friendly docs:
http://perl.apache.org/search/swish.cgi?query=prepare_cached&sbm=SecE&submit=search
__________________________________________________________________
Stas Bekman JAm_pH ------> Just Another mod_perl Hacker
http://stason.org/ mod_perl Guide ---> http://perl.apache.org
mailto:stas@stason.org http://use.perl.org http://apacheweek.com
http://modperlbook.org http://apache.org http://ticketmaster.com
Re: MySQL & prepared sql statements?
Posted by David Wheeler <da...@wheeler.net>.
On Sunday, November 24, 2002, at 01:08 PM, Bas A.Schulte wrote:
> I'm wondering if I would gain (some) performance by preparing my sql
> statements once during ChildInit when using MySQL? Does it cache
> execution plans for sql statements? I know it makes a big difference
> on Oracle but I'm not sure what MySQL is like in this respect.
MySQL doesn't have a prepared statement feature, so you wouldn't get
the same kind of performance gain that you would with Oracle. That
said, DBD::mysql does a bit of munging with your statements, and if you
use prepare(), it does that munging only once per statement.
The upshot: Use prepare() whenever possible to maximize performance.
Even better, used prepare_cached() for statements you'll be using a
lot, and then they'll be prepared only the first time prepare() is
called (per Apache child). Doing so will also make it easier to gain
the performance benefit if you later decide to switch to a database
such as Oracle that supports prepared statements in the database.
Regards,
David
--
David Wheeler AIM: dwTheory
david@wheeler.net ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e
Jabber: Theory@jabber.org