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.

1 files changed, 12 insertions, 2 deletions

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 #