By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,988 Members | 1,068 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,988 IT Pros & Developers. It's quick & easy.

MySQL prepare statement performance bottom-neck

P: n/a
Hi,

When doing mysql query (SELECT statements) in php, we often use prepare
statement to prevent SQL injection. However, I just noticed that the
prepare statements can SLOW the number of queries per second by a
factor of 2 times (max).

So are there any faster method that can prevent SQL injection, but has
a better performance?

Thanks.

Jun 30 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a

If it is MySQL < 4.1, then you are using emulated prepared statements,
which may be causing the slowdown. It wouldn't surprise me if PEAR was
doing some funny regex's to parse the queries, escape the values, etc.

If you're using a database abstraction library, maybe try switching the
abstraction library (See PDO and Adodb, among others), it might speed
things up.

It should also be noted that using prepare/execute requires 2 trips to
the server
(http://dev.mysql.com/tech-resources/...atements.html),
since it has to send it to be parsed, then send it to be executed.
This would account for the exact factor of 2.
ho******@gmail.com wrote:
Hi,

When doing mysql query (SELECT statements) in php, we often use prepare
statement to prevent SQL injection. However, I just noticed that the
prepare statements can SLOW the number of queries per second by a
factor of 2 times (max).

So are there any faster method that can prevent SQL injection, but has
a better performance?

Thanks.


Jun 30 '06 #2

P: n/a

Richard Levasseur 寫道:
If it is MySQL < 4.1, then you are using emulated prepared statements,
which may be causing the slowdown. It wouldn't surprise me if PEAR was
doing some funny regex's to parse the queries, escape the values, etc.

If you're using a database abstraction library, maybe try switching the
abstraction library (See PDO and Adodb, among others), it might speed
things up.

It should also be noted that using prepare/execute requires 2 trips to
the server
(http://dev.mysql.com/tech-resources/...atements.html),
since it has to send it to be parsed, then send it to be executed.
This would account for the exact factor of 2.
ho******@gmail.com wrote:
Hi,

When doing mysql query (SELECT statements) in php, we often use prepare
statement to prevent SQL injection. However, I just noticed that the
prepare statements can SLOW the number of queries per second by a
factor of 2 times (max).

So are there any faster method that can prevent SQL injection, but has
a better performance?

Thanks.


Thanks...

So is that means in order to prevent SQL injection, we must need this
kind of overhead?

Jul 1 '06 #3

P: n/a

ho******@gmail.com wrote:
Richard Levasseur 寫道:
If it is MySQL < 4.1, then you are using emulated prepared statements,
which may be causing the slowdown. It wouldn't surprise me if PEAR was
doing some funny regex's to parse the queries, escape the values, etc.

If you're using a database abstraction library, maybe try switching the
abstraction library (See PDO and Adodb, among others), it might speed
things up.

It should also be noted that using prepare/execute requires 2 trips to
the server
(http://dev.mysql.com/tech-resources/...atements.html),
since it has to send it to be parsed, then send it to be executed.
This would account for the exact factor of 2.
ho******@gmail.com wrote:
Hi,

When doing mysql query (SELECT statements) in php, we often use prepare
statement to prevent SQL injection. However, I just noticed that the
prepare statements can SLOW the number of queries per second by a
factor of 2 times (max).

So are there any faster method that can prevent SQL injection, but has
a better performance?

Thanks.


Thanks...

So is that means in order to prevent SQL injection, we must need this
kind of overhead?


You could manually escape the values before you query, its just a lot
more work to $databaseHandle->escape($value) for every user submitted
value. This would most likely solve the performance problem.

Jul 1 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.