Mysql
Explore the world of MySQL and optimize your database performance with expert tips and techniques. Enhance your data management skills today with A2A PRODUCTION web solutions
Understanding the MySQL Information Schema Database
The INFORMATION_SCHEMA database is where each MySQL instance stores information about all the other databases that the MySQL server maintains. Also sometimes referred to as the data dictionary and system catalog, it’s the ideal place to lookup information such as the name of a database or table, the data type of a column, or access privileges. Today’s article will provide an overview of the INFORMATION_SCHEMA database as well as some practical examples of its many uses.
The METADATA Databases
As of MySQL 5, the information_schema is now listed in the database list when you execute the show databases command:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| business_contacts |
| company |
| customers |
| information_schema |
| mysql |
| performance_schema |
| sonar |
+--------------------+
You’ll also notice the mysql and performance_schema databases, which also store database metadata.
Likewise, issuing a show tables command on the information_schema database will give you a list of all its tables:
mysql> use information_schema; mysql> show tables;
+---------------------------------------+
| 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 |
+---------------------------------------+
In actuality, these tables are read-only views. Hence, you cannot change its structure or modify its data. Their sole purpose is to provide information about the database system. In fact, all tables in the INFORMATION_SCHEMA database are stored directly in memory as MEMORY storage engine tables. Thus, when mysqld is shutdown, all information_schema tables are dropped. Then, when MySQL (mysqld) is restarted, all information_schema tables are recreated as TEMPORARY tables and repopulated with metadata for every table.
Removing the Metadata Databases from Query Results
Most of the time, when querying the INFORMATION_SCHEMA database, you’ll only be interested in your own databases, not the metadata ones. To remove them, include this filter in the where clause:
AND information_schema.TABLES.table_schema NOT IN(‘information_schema’,’mysql’,’performance_schema’);
It’s present in most of the following examples…
Example #1: List All Tables without Primary Key
Here’s a query that uses the TABLE_CONSTRAINTS view to check for table names whose constraint_name field is null:
SELECT CONCAT(t.table_schema,’.’,t.table_name) as table_nameFROM information_schema.TABLES tLEFT JOIN information_schema.TABLE_CONSTRAINTS tcON t.table_schema = tc.table_schemaAND t.table_name = tc.table_nameAND tc.constraint_type = ‘PRIMARY KEY’WHERE tc.constraint_name IS NULLAND t.table_type = ‘BASE TABLE’AND t.table_schema not in(‘information_schema’,’mysql’,’performance_schema’);
+----------------------------------+
| table_name |
+----------------------------------+
| customers.orders |
| business_contacts.company_info |
| sonar.schema_migrations |
| sonar.resource_index |
| sonar.groups_users |
| sonar.duplications_index |
| sonar.characteristic_edges |
| sonar.action_plans_reviews |
+----------------------------------+
Example #2: Display Tables with More Than a Given Number of Rows
Sometimes it’s useful to see which tables’ size exceeds a given number of rows. That may signal the time to archive some of the older data for performance reasons. The following SQL statement returns all the tables in your user databases that contain more than one thousand rows:
SELECT CONCAT(table_schema,’.’,table_name) as table_name, table_rowsFROM information_schema.tables WHERE table_rows > 1000 AND table_schema not in(‘information_schema’,’mysql’,’performance_schema’);ORDER BY table_rows desc;
+----------------------------------+------------+
| table_name | table_rows |
+----------------------------------+------------+
| customers.orders | 2007 |
| customers.contact_info | 1245 |
| customers.rewards_points | 2147 |
| business_contacts.company_info | 1340 |
| business_contacts.phone_numbers | 1712 |
| sonar.project_measures | 178618 |
| sonar.resource_index | 110328 |
| sonar.rule_failures | 40793 |
+----------------------------------+------------+
For greater flexibility, this query can be placed in a stored procedure. That way, you can provide the number of rows via an input parameter:
CREATE PROCEDURE `pr_display_tables_with_more_than_n_rows` (IN `numberOfRows` BIGINT)LANGUAGE SQLNOT DETERMINISTICREADS SQL DATASQL SECURITY DEFINERCOMMENT ‘Displays tables with more than given number of rows.’BEGIN SELECT CONCAT(table_schema,’.’,table_name) as table_name, table_rows FROM information_schema.tables WHERE table_rows > 1000 AND table_schema not in(‘information_schema’,’mysql’,’performance_schema’); ORDER BY table_rows desc;END
Example #3: Display Access Privileges
You can use the INFORMATION_SCHEMA to review users’ privileges in order to help you decide which rights to grant or revoke. Moreover, the INFORMATION_SCHEMA database can provide views at varying levels of granularity. While the SHOW PRIVILEGES command shows the system privileges the server supports, the INFORMATION_SCHEMA.SCHEMA_PRIVILEGES view lists which database users have been assigned those system privileges:
SELECT grantee, privilege_type, is_grantableFROM information_schema.schema_privilegesWHERE table_schema = ‘sonar’;
+---------------------+-------------------------+--------------+
| grantee | privilege_type | is_grantable |
+---------------------+-------------------------+--------------+
| 'sonar'@'localhost' | SELECT | NO |
| 'sonar'@'localhost' | INSERT | NO |
| 'sonar'@'localhost' | UPDATE | NO |
| 'sonar'@'localhost' | DELETE | NO |
| 'sonar'@'localhost' | CREATE | NO |
| 'sonar'@'localhost' | DROP | NO |
| 'sonar'@'localhost' | REFERENCES | NO |
| 'sonar'@'localhost' | INDEX | NO |
| 'sonar'@'localhost' | ALTER | NO |
| 'sonar'@'localhost' | CREATE TEMPORARY TABLES | NO |
| 'sonar'@'localhost' | LOCK TABLES | NO |
| 'sonar'@'localhost' | EXECUTE | NO |
| 'sonar'@'localhost' | CREATE VIEW | NO |
| 'sonar'@'localhost' | SHOW VIEW | NO |
| 'sonar'@'localhost' | CREATE ROUTINE | NO |
| 'sonar'@'localhost' | ALTER ROUTINE | NO |
| 'sonar'@'localhost' | EVENT | NO |
| 'sonar'@'localhost' | TRIGGER | NO |
+---------------------+-------------------------+--------------+
Example #4: Find Long Running Queries
In version 5.1, MySQL added the pocesslist to the INFORMATION_SCHEMA database. It shows information on all the currently running processes:
Show processlist;
+----+----------+----------------+------+---------+------+-------+-------------------+
| Id | User |Host | db | Command | Time | State | Info |
+----+----------+----------------+------+---------+------+-------+-------------------+
| 1 | root | localhost:1715 | | Sleep | 73 | | |
| 2 | root | localhost:1716 | test | Query | 0 | | show processlist |
| 5 | sonar | localhost:1835 | test | Sleep | 2359 | | |
+----+----------+----------------+------+---------+------+-------+-------------------+
We can query the processlist to find long running queries. The Time field is in seconds, so we can fetch all the queries that are running for over10 minutes by comparing it to the expression of sixty seconds times ten:
SELECT * FROM information_schema.processlist WHERE COMMAND = ‘Query’ AND time > 60 * 10;
Conclusion
There are still many more excellent uses for INFORMATION_SCHEMA that will have to wait until another day. Until then, you can run a SELECT * on any of the INFORMATION_SCHEMA views to see what information is contained therein.
