mysql permanently prepared statements

Question

i am looking to accelerate some queries using prepared statements on a high traffic site. what i don't think i'm understanding correctly is the benefit from using prepared statements unless they can stay prepared over multiple connections. it looks like this is not possible with PDO which also does not allow persistent connections. but the persistent connection functions don't allow PDO.

lets say for arguments sake i am running a query 5,000 times per second: SELECT * FROM some_table WHERE some_column LIKE 'some_value'

from what i understand, PDO would prevent mysql from re-compiling and evaluating the query if i were to change "some_value" each time i needed to query. i also understand that "some_value" could be transmitted in binary instead of ASCII to save bandwidth, but it wouldn't be saving much if i had to send the entire query each time i open the connection.

also from what i have read, stored procedures are not the solution, because those do not stay compiled through multiple connections either.

is there any solution to this problem? storing a prepared statement on the server somewhere and having it stay compiled in memory and ready to fire as soon as it receives the variables?

is there any way to make this happen by combining connection pooling with PDO? (although i have also heard connection pooling is not ideal because it can cause blocking in certain conditions)

If you wish to use persistent connections, you must set PDO::ATTR_PERSISTENT in the array of driver options passed to the PDO constructor. If setting this attribute with PDO::setAttribute() after instantiation of the object, the driver will not use persistent connections. — Roman Newaza

Ross Smith II Ross Smith II · Accepted Answer · 2012-07-23T05:46:58

After running numerous benchmarks, we found that prepared statements prepared on the server provided us the greatest speed benefits. Here's an example:

DROP PROCEDURE IF EXISTS get_user;

DELIMITER //

CREATE PROCEDURE get_user(IN v_user VARCHAR(255))
DETERMINISTIC
READS SQL DATA
SQL SECURITY INVOKER
COMMENT ''
proc: BEGIN
    SET @user = v_user;

    IF ISNULL(@get_user_prepared) THEN
        SET @get_user_prepared = TRUE;

        SET @sql = "SELECT * FROM mysql.user WHERE user = ?";

        PREPARE get_user_stmt FROM @sql;
    END IF;

    EXECUTE get_user_stmt USING @user;
END;
//

DELIMITER ;

mysql permanently prepared statements

3 Answers