There are two things to consider
Stored Procedures/Functions are physically stored in mysql.proc as a MyISAM table
mysql> show create table `mysql.proc`\G
*************************** 1. row ***************************
Table: proc
Create Table: CREATE TABLE `proc` (
`db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`name` char(64) NOT NULL DEFAULT '',
`type` enum('FUNCTION','PROCEDURE') NOT NULL,
`specific_name` char(64) NOT NULL DEFAULT '',
`language` enum('SQL') NOT NULL DEFAULT 'SQL',
`sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
`is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
`security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
`param_list` blob NOT NULL,
`returns` longblob NOT NULL,
`body` longblob NOT NULL,
`definer` char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
`comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`body_utf8` longblob,
PRIMARY KEY (`db`,`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures'
1 row in set (0.00 sec)
mysql>
Stored Procedures/Functions are physically and logically stored in information_schema.routines as a temporary MyISAM table:
mysql> show create table information_schema.routines\G
*************************** 1. row ***************************
Table: ROUTINES
Create Table: CREATE TEMPORARY TABLE `ROUTINES` (
`SPECIFIC_NAME` varchar(64) NOT NULL DEFAULT '',
`ROUTINE_CATALOG` varchar(512) NOT NULL DEFAULT '',
`ROUTINE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`ROUTINE_NAME` varchar(64) NOT NULL DEFAULT '',
`ROUTINE_TYPE` varchar(9) NOT NULL DEFAULT '',
`DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
`CHARACTER_MAXIMUM_LENGTH` int(21) DEFAULT NULL,
`CHARACTER_OCTET_LENGTH` int(21) DEFAULT NULL,
`NUMERIC_PRECISION` int(21) DEFAULT NULL,
`NUMERIC_SCALE` int(21) DEFAULT NULL,
`CHARACTER_SET_NAME` varchar(64) DEFAULT NULL,
`COLLATION_NAME` varchar(64) DEFAULT NULL,
`DTD_IDENTIFIER` longtext,
`ROUTINE_BODY` varchar(8) NOT NULL DEFAULT '',
`ROUTINE_DEFINITION` longtext,
`EXTERNAL_NAME` varchar(64) DEFAULT NULL,
`EXTERNAL_LANGUAGE` varchar(64) DEFAULT NULL,
`PARAMETER_STYLE` varchar(8) NOT NULL DEFAULT '',
`IS_DETERMINISTIC` varchar(3) NOT NULL DEFAULT '',
`SQL_DATA_ACCESS` varchar(64) NOT NULL DEFAULT '',
`SQL_PATH` varchar(64) DEFAULT NULL,
`SECURITY_TYPE` varchar(7) NOT NULL DEFAULT '',
`CREATED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`LAST_ALTERED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`SQL_MODE` varchar(8192) NOT NULL DEFAULT '',
`ROUTINE_COMMENT` longtext NOT NULL,
`DEFINER` varchar(77) NOT NULL DEFAULT '',
`CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '',
`COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT '',
`DATABASE_COLLATION` varchar(32) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
In reality, there absolutely no need to read all Stored Procedures/Functions as mysqld has already done this. In fact, once you shutdown mysql,
information_schema.routines will get droppedmysql.proc remains in existence
What mysqld actually does on startup is populate information_schema.routines from mysql.proc.
Triggers are similarly stored. Here is information_schema.triggers
mysql> show create table information_schema.triggers\G
*************************** 1. row ***************************
Table: TRIGGERS
Create Table: CREATE TEMPORARY TABLE `TRIGGERS` (
`TRIGGER_CATALOG` varchar(512) NOT NULL DEFAULT '',
`TRIGGER_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`TRIGGER_NAME` varchar(64) NOT NULL DEFAULT '',
`EVENT_MANIPULATION` varchar(6) NOT NULL DEFAULT '',
`EVENT_OBJECT_CATALOG` varchar(512) NOT NULL DEFAULT '',
`EVENT_OBJECT_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`EVENT_OBJECT_TABLE` varchar(64) NOT NULL DEFAULT '',
`ACTION_ORDER` bigint(4) NOT NULL DEFAULT '0',
`ACTION_CONDITION` longtext,
`ACTION_STATEMENT` longtext NOT NULL,
`ACTION_ORIENTATION` varchar(9) NOT NULL DEFAULT '',
`ACTION_TIMING` varchar(6) NOT NULL DEFAULT '',
`ACTION_REFERENCE_OLD_TABLE` varchar(64) DEFAULT NULL,
`ACTION_REFERENCE_NEW_TABLE` varchar(64) DEFAULT NULL,
`ACTION_REFERENCE_OLD_ROW` varchar(3) NOT NULL DEFAULT '',
`ACTION_REFERENCE_NEW_ROW` varchar(3) NOT NULL DEFAULT '',
`CREATED` datetime DEFAULT NULL,
`SQL_MODE` varchar(8192) NOT NULL DEFAULT '',
`DEFINER` varchar(77) NOT NULL DEFAULT '',
`CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '',
`COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT '',
`DATABASE_COLLATION` varchar(32) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
The physical manifestation of triggers resides in database folder of the table that own the trigger. Trigger code resides in a file with the extension .TRG.
For example, for a given MyISAM table mydb.mytable that has triggers, you will have
/var/lib/mysql/mydb/mytable.frm/var/lib/mysql/mydb/mytable.MYD/var/lib/mysql/mydb/mytable.MYI/var/lib/mysql/mydb/mytable.TRG
Although you may not get good results, if you still want to experiment with reading triggers and stored procedures immediately after startup:
STEP 01) Create a script to read those tables
cd /var/lib/mysql
echo "SELECT * FROM information_schema.routines;" > init_file.sql
echo "SELECT * FROM information_schema.triggers;" >> init_file.sql
STEP 02) Add this line to /etc/my.cnf
init-file=/var/lib/mysql/init_file.sql
STEP 03) No need to restart mysqld. Just run the script by hand
mysql> source /var/lib/mysql/init_file.sql
That's all I can suggest. Since routines and triggers are preloaded, this suggestion may not yield any additional performance. You can try anyway and please with your findings.
