From d9092e3de7b6868403f2611508249aa0bcd9022a Mon Sep 17 00:00:00 2001 From: Oleg Smirnov Date: Mon, 19 Sep 2022 21:36:09 +0700 Subject: MDEV-29104 Optimize queries to INFORMATION_SCHEMA.PARAMETERS/ROUTINES For queries like "SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='proc_name'" and "SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='proc_name'" there is a possibility to avoid loading of the stored procedure code and parsing it to retrieve parameters. If the name of the procedure/function is specified explicitly then it is possible to filter out routines that do not match at an early stage. --- mysql-test/main/information_schema_parameters.test | 14 ++++++++++++-- 1 file changed, 12 insertions(+), 2 deletions(-) (limited to 'mysql-test/main/information_schema_parameters.test') diff --git a/mysql-test/main/information_schema_parameters.test b/mysql-test/main/information_schema_parameters.test index c53ecce8b2b..0c9d94e97f6 100644 --- a/mysql-test/main/information_schema_parameters.test +++ b/mysql-test/main/information_schema_parameters.test @@ -292,11 +292,21 @@ CREATE FUNCTION test_func5 (s CHAR(20)) RETURNS VARCHAR(30) RETURN CONCAT('XYZ, ' ,s); --echo # ---echo # We cannot use the index due to CONCAT() +--echo # We cannot use the index due to missing condition on SPECIFIC_SCHEMA, +--echo # but we will use SPECIFIC_NAME for filtering records from mysql.proc +FLUSH STATUS; +query_vertical SELECT * FROM INFORMATION_SCHEMA.PARAMETERS + WHERE SPECIFIC_NAME = 'test_func5'; +SHOW STATUS LIKE 'handler_read%next'; + +--echo # +--echo # We cannot use the index due to CONCAT(), and filtering by SPECIFIC_NAME +--echo # does not work either since SPECIFIC_NAME = 'not_existing_proc'. See +--echo # the difference in counters in comparison to the previous test FLUSH STATUS; query_vertical SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE CONCAT(SPECIFIC_SCHEMA) = 'i_s_parameters_test' - AND SPECIFIC_NAME = 'test_func5'; + AND SPECIFIC_NAME = 'not_existing_proc'; SHOW STATUS LIKE 'handler_read%next'; --echo # -- cgit v1.2.1