Geek asks and answers

Mysql constantly prepared statements

I want to speed up some queries using prepared statements on a high traffic site. what I don’t think I understand correctly is the advantage of using prepared statements if they cannot remain ready for multiple connections. this seems to be impossible with PDO, which also does not allow persistent connections. but persistent join functions do not allow PDO.

allows you to talk for arguments, I run the query 5,000 times per second: SELECT * FROM some_table WHERE some_column LIKE 'some_value'

from what I understand, PDO will prevent mysql from recompiling and query evaluation if I change "some_value" every time I need to query. I also understand that "some_value" can be passed in binary instead of ASCII to save bandwidth, but it will not save much if I had to send the whole request every time I open the connection.

also from what I read, stored procedures are not a solution because they do not remain compiled over multiple connections.

is there any solution to this problem? keeping the finished statement on the server somewhere and keeping it compiled in memory and ready to fire as soon as it receives the variables?

, PDO? ( , , )

+5
3

, , , . :

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 ;
+5

MySQL , . , 5k/s. Memcached , Redis. , , .

0

No, there is no way to use persistent trained statements.

However, there is an ideal solution for a query running 5,000 times per second - Handlersocket

0
source

More articles:


All Articles