MySql: Discovering and PROFILING information_schema

I recently spent an environment mysql version 5.0.32 to a later version already includes the information_schema database with metadata tables equivalent to SHOW statements and some previously available.

"INFORMATION_SCHEMA is the information database, which stores information about all the other databases maintained by the MySQL server. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not tables, so you can not see no files associated with them. Each MySQL user has the right to access these tables, but only the records that correspond to the objects that have permission to access. "

I will not entertain explaining each of them but I would like to reflect a little on the subject.One of the main advantages I see with the addition of these tables is that we can play like the Oracle data dictionary to build ddl or dml statements dynamically. At the moment that it is a what. The full list of tables is as follows:

+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| KEY_COLUMN_USAGE |
| PROFILING |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+ 

I will not say much more ...But another interesting Cosic which has led me PROFILING table is that activating a "profiler" for the current session can get more detail on which employs the time it takes to run the query. Something like Microsoft SqlServer Profiler but simpler.Here's how:

/ / Enabled for the current session
mysql> SELECT @ @ profiling;
+---------------------+
| @ @ Profiling |
+---------------------+
| 0 |
+---------------------+
1 row in set (0.00 sec)

mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW PROFILES;
+----------+----------+--------------------------- --- +
| Query_ID | Duration | Query |
+----------+----------+--------------------------- --- +
| 0 | 0.000088 | SET PROFILING = 1 |
| 1 | 0.000136 | DROP TABLE IF EXISTS t1 | | 2 | 0.011947 | CREATE TABLE t1 (id INT) | +----------+----------+----------- -------------------+ 3 rows in set (0.00 sec) mysql> SHOW PROFILE; +---------------- ------+--------------------+ | Status | Duration | +---------------- ------+--------------------+ | checking permissions | 0.000040 | | Creating table | 0.000056 | | After create | 0.011363 | | query end | 0.000375 | | Freeing items | 0.000089 | | logging slow query | 0.000019 | | cleaning up | 0.000005 | +----------------------+----- ---------------+ 7 rows in set (0.00 sec) mysql> SHOW PROFILE FOR QUERY 1; +----------------- ---+-------------+ | Status |Duration | +--------------------+-------------+ | query end | 0.000107 | | Freeing items | 0.000008 | | logging slow query | 0.000015 | | cleaning up | 0.000006 | +--------------------+-------------+ 4 rows in set (0.00 sec) mysql> SHOW PROFILE CPU FOR QUERY 2; +----------------------+---------- +----------+------------+ | Status | Duration | CPU_user | CPU_system | +--------------- -------+----------+----------+------------+ | checking permissions | 0.000040 | 0.000038 | 0.000002 | | Creating table | 0.000056 | 0.000028 | 0.000028 | | After create | 0.011363 | 0.000217 | 0.001571 | | query end | 0.000375 | 0.000013 | 0.000028 | | Freeing items | 0.000089 | 0.000010 | 0.000014 | | logging slow query | 0.000019 | 0.000009 | 0.000010 | | cleaning up | 0.000005 | 0.000003 | 0.000002 | +----------------------+----------+- ---------+------------+ 7 rows in set (0.00 sec) 

This could be used with queries that for example appear on our slow-query log or to detect rare things dll sentences that take too long to run etc ...But I find useful.

More about profiling in http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html
More on information_schema in http://dev.mysql.com/doc/refman/5.0/es/information-schema.html