PHP/MySQL Prepared Statements - Can one user benefit from another user's prepared query?

Is there any kind of server-side optimization (like in a MySQL database) that allows one user's prepared query to benefit from another user's previously prepared query? (The emphasis here is on possible performance improvements BETWEEN users, each query running during a separate php script.)

Example:

Query for user 1: SELECT * FROM SomeTable WHERE item1 = ? and item2 = ?

item 1 = x item 2 = y

Query for user 2: SELECT * FROM SomeTable WHERE item1 = ? and item2 = ? (same query as above)

item 1 = y item 2 = z

If this identical prepared query (though with different parameters each time) will only be executed once for each of these different users, is there any potential performance gain from using a prepared query?

Or would it just be better to use the real_escape_string function on items 1 and 2, and just put them right into the query. This would avoid making two trips to the db, one to check the validity of the prepared query, and the other to actually execute the query.

I'm not all that worried about SQL injection, since with proper escaping, either method should be just as safe. (Right?) I'm honestly just开发者_如何学C wondering if it's worth using prepared statements if I'm not iterating through the execution of the same prepared query multiple times within the same php script.

Thanks!

It depends on which version of MySQL you are using. See this page : http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html If you are using MySQL before version 5.1.17, prepared statements won't be cached, and the use of MySQL query cache gives better results than the use of prepared statements, so you'd better worry about whether you are using this cache in an optimized way or not. Otherwise, I think the answer to your question is yes, because instead of doing this twice:

1. send the request to the server
2. compile the request
3. execution plan
4. execute request
5. send result back

you would do this:

PREPARE :

1. send the request to the server
2. compile the request
3. execution plan
4. store the compiled request in memory
5. return a request identifier

EXECUTE with first series of parameters:

1. ask for execution with the identifier
2. execute
3. return result

EXECUTE another series of parameters: 1. ask for execution with the identifier 2. execute 3. return result

(all this was translated from this french course on prepared statements) As you see, you'll have a real performance gain if:

• you execute this request a lot
• the request is complex and takes time to compile
• the query cache is used

you could always call a stored procedure - it's just a single call :)

drop procedure if exists get_user;
delimiter #

create procedure get_user
(
in p_user_id int unsigned
)
proc_main:begin
    select u.* from users u where u.user_id = p_user_id;
end proc_main#

delimiter ;

call get_user(1);
call get_user(3);
call get_user(6);

The problem here is that your two instances cannot share any code (without putting a lot of effort into it). Preparing the query twice should be negligible. If there are actual performance bottlenecks, the problem is probably something else.