{"id":10435,"date":"2019-10-25T11:13:14","date_gmt":"2019-10-25T11:13:14","guid":{"rendered":"https:\/\/mpgico.com\/new\/?page_id=10435"},"modified":"2019-10-25T11:13:14","modified_gmt":"2019-10-25T11:13:14","slug":"mysql","status":"publish","type":"page","link":"https:\/\/mpgico.com\/new\/mysql\/","title":{"rendered":"Mysql"},"content":{"rendered":"

[vc_row][vc_column][vc_column_text]<\/p>\n

Understanding the MySQL Information Schema Database<\/h1>\n

The INFORMATION_SCHEMA database is where each MySQL instance stores information about all the other databases that the MySQL server maintains.\u00a0 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.<\/p>\n

The METADATA Databases<\/h2>\n

As of MySQL 5, the information_schema is now listed in the database list when you execute the\u00a0show databases\u00a0<\/em>command:<\/p>\n

mysql> show databases;<\/p>\n

+--------------------+ <\/span><\/pre>\n
\u00a0| Database\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0| <\/span><\/pre>\n
\u00a0+--------------------+ <\/span><\/pre>\n
\u00a0| business_contacts\u00a0 |<\/span><\/pre>\n
 | company\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 | customers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 | information_schema |<\/span><\/pre>\n
 | mysql\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 | performance_schema |<\/span><\/pre>\n
 | sonar\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 +--------------------+ <\/span><\/pre>\n
You’ll also notice the mysql and performance_schema databases, which also store database metadata.<\/p>\n
Likewise, issuing a show tables command on the information_schema database will give you a list of all its tables:<\/p>\n
mysql> use information_schema; mysql> show tables;<\/p>\n
\u00a0+---------------------------------------+<\/span><\/pre>\n
 | Tables_in_information_schema\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 +---------------------------------------+<\/span><\/pre>\n
 | CHARACTER_SETS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 | COLLATIONS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 | COLLATION_CHARACTER_SET_APPLICABILITY |<\/span><\/pre>\n
 | COLUMNS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 | COLUMN_PRIVILEGES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 | KEY_COLUMN_USAGE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|<\/span><\/pre>\n
 | PROFILING\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 | ROUTINES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 | SCHEMATA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 | SCHEMA_PRIVILEGES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 | STATISTICS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 | TABLES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0|<\/span><\/pre>\n
 | TABLE_CONSTRAINTS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 | TABLE_PRIVILEGES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 | TRIGGERS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 | USER_PRIVILEGES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 | VIEWS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 +---------------------------------------+<\/span><\/pre>\n
In actuality, these tables are read-only views.\u00a0 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.<\/p>\n
Removing the Metadata Databases from Query Results<\/h2>\n
Most of the time, when querying the INFORMATION_SCHEMA database, you’ll only be interested in your own databases, not the metadata ones.\u00a0 To remove them, include this filter in the where clause:<\/p>\n
AND information_schema.TABLES.table_schema \u00a0\u00a0\u00a0\u00a0 NOT IN(‘information_schema’,’mysql’,’performance_schema’);<\/p>\n
It’s present in most of the following examples\u2026<\/p>\n
Example #1: List All Tables without Primary Key<\/h2>\n
Here’s a query that uses the TABLE_CONSTRAINTS view to check for table names whose constraint_name field is null:<\/p>\n
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’);<\/p>\n
+----------------------------------+<\/span><\/pre>\n
 | table_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | <\/span><\/pre>\n
\u00a0+----------------------------------+<\/span><\/pre>\n
| customers.orders\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| business_contacts.company_info\u00a0\u00a0 |<\/span><\/pre>\n
 | sonar.schema_migrations\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| sonar.resource_index\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| sonar.groups_users\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| sonar.duplications_index\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| sonar.characteristic_edges\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| sonar.action_plans_reviews\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 +----------------------------------+<\/span><\/pre>\n
Example #2: Display Tables with More Than a Given Number of Rows<\/p>\n
Sometimes it’s useful to see which tables’ size exceeds a given number of rows.\u00a0 That may signal the time to archive some of the older data for performance reasons.\u00a0 The following SQL statement returns all the tables in your user databases that contain more than one thousand rows:<\/p>\n
SELECT CONCAT(table_schema,’.’,table_name) as table_name,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 table_rowsFROM\u00a0\u00a0 information_schema.tables \u00a0WHERE table_rows > 1000 AND table_schema not in(‘information_schema’,’mysql’,’performance_schema’);ORDER BY table_rows desc;<\/p>\n
+----------------------------------+------------+ <\/span><\/pre>\n
\u00a0| table_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | table_rows | <\/span><\/pre>\n
\u00a0+----------------------------------+------------+ <\/span><\/pre>\n
\u00a0| customers.orders\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 2007\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | <\/span><\/pre>\n
\u00a0| customers.contact_info\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0| 1245\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | <\/span><\/pre>\n
\u00a0| customers.rewards_points\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 2147\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | <\/span><\/pre>\n
\u00a0| business_contacts.company_info\u00a0\u00a0 | 1340\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | <\/span><\/pre>\n
\u00a0| business_contacts.phone_numbers\u00a0 | 1712\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | <\/span><\/pre>\n
\u00a0| sonar.project_measures \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| 178618\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| sonar.resource_index \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| 110328\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| sonar.rule_failures \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| 40793\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
 +----------------------------------+------------+ <\/span><\/pre>\n
For greater flexibility, this query can be placed in a stored procedure.\u00a0 That way, you can provide the number of rows via an input parameter:<\/p>\n
CREATE PROCEDURE `pr_display_tables_with_more_than_n_rows`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (IN `numberOfRows` BIGINT)LANGUAGE SQLNOT DETERMINISTICREADS SQL DATASQL SECURITY DEFINERCOMMENT ‘Displays tables with more than given number of rows.’BEGIN\u00a0 SELECT CONCAT(table_schema,’.’,table_name) as table_name,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 table_rows\u00a0 FROM\u00a0\u00a0 information_schema.tables \u00a0\u00a0WHERE table_rows > 1000\u00a0 AND table_schema not in(‘information_schema’,’mysql’,’performance_schema’);\u00a0 ORDER BY table_rows desc;END<\/p>\n
Example #3: Display Access Privileges<\/h2>\n
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. \u00a0While the SHOW PRIVILEGES command shows the system privileges the server\u00a0supports<\/em>, the INFORMATION_SCHEMA.SCHEMA_PRIVILEGES view lists which database users have been\u00a0assigned<\/em>\u00a0those system privileges:<\/p>\n
SELECT grantee, \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0privilege_type, \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0is_grantableFROM\u00a0\u00a0 information_schema.schema_privilegesWHERE\u00a0 table_schema = ‘sonar’;<\/p>\n
+---------------------+-------------------------+--------------+<\/span><\/pre>\n
| grantee\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | privilege_type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | is_grantable |<\/span><\/pre>\n
+---------------------+-------------------------+--------------+<\/span><\/pre>\n
| 'sonar'@'localhost' | SELECT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| 'sonar'@'localhost' | INSERT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| 'sonar'@'localhost' | UPDATE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| 'sonar'@'localhost' | DELETE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| 'sonar'@'localhost' | CREATE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| 'sonar'@'localhost' | DROP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| 'sonar'@'localhost' | REFERENCES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| 'sonar'@'localhost' | INDEX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| 'sonar'@'localhost' | ALTER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| 'sonar'@'localhost' | CREATE TEMPORARY TABLES | NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| 'sonar'@'localhost' | LOCK TABLES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| 'sonar'@'localhost' | EXECUTE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| 'sonar'@'localhost' | CREATE VIEW\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| 'sonar'@'localhost' | SHOW VIEW\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| 'sonar'@'localhost' | CREATE ROUTINE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| 'sonar'@'localhost' | ALTER ROUTINE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| 'sonar'@'localhost' | EVENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| 'sonar'@'localhost' | TRIGGER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
+---------------------+-------------------------+--------------+<\/span><\/pre>\n
Example #4: Find Long Running Queries<\/h2>\n
In version 5.1, MySQL added the pocesslist to the INFORMATION_SCHEMA database. It shows information on all the currently running processes:<\/p>\n
Show processlist;<\/p>\n
 <\/p>\n
+----+----------+----------------+------+---------+------+-------+-------------------+<\/span><\/pre>\n
| Id | User\u00a0\u00a0\u00a0\u00a0 |Host\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | db\u00a0\u00a0 | Command | Time | State | Info\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
+----+----------+----------------+------+---------+------+-------+-------------------+<\/span><\/pre>\n
| 1\u00a0 | root\u00a0\u00a0\u00a0\u00a0 | localhost:1715 |\u00a0\u00a0\u00a0\u00a0\u00a0 | Sleep\u00a0\u00a0 | 73\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
| 2\u00a0 | root\u00a0\u00a0\u00a0\u00a0 | localhost:1716 | test | Query\u00a0\u00a0 | 0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | show processlist\u00a0 |<\/span><\/pre>\n
| 5\u00a0 | sonar\u00a0\u00a0\u00a0 | localhost:1835 | test | Sleep\u00a0\u00a0 | 2359 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/pre>\n
+----+----------+----------------+------+---------+------+-------+-------------------+<\/span><\/pre>\n
We can query the processlist to find long running queries.\u00a0 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:<\/p>\n
SELECT * FROM information_schema.processlist WHERE COMMAND = ‘Query’\u00a0 AND\u00a0\u00a0 time\u00a0\u00a0\u00a0 > 60 * 10;<\/p>\n
Conclusion<\/h2>\n
There are still many more excellent uses for INFORMATION_SCHEMA that will have to wait until another day.\u00a0 Until then, you can run a SELECT * on any of the INFORMATION_SCHEMA views to see what information is contained therein.<\/p>\n
[\/vc_column_text]
Error:<\/strong> Contact form not found.<\/p>[\/vc_column][\/vc_row]<\/p>\n<\/section>","protected":false},"excerpt":{"rendered":"
[vc_row][vc_column][vc_column_text] 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.\u00a0 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-10435","page","type-page","status-publish","hentry","wd-post "],"yoast_head":"\nMPGICO, Digital Advertising Solutions Lebanon,Web development Lebanon, Advertising and Marketing solutions Lebanon, web solutions Lebanon, SEO Lebanon, Mobile Applications Lebanon, Web development Qatar, Advertising and Marketing solutions Qatar, web solutions Qatar, SEO Doha Qatar, Mobile Applications Qatar<\/title>\n<meta name=\"description\" content=\"Digital Advertising Solutions Lebanon,Web development Lebanon, Advertising and Marketing solutions Lebanon, web solutions Lebanon, SEO Lebanon\" \/>\n<meta name=\"robots\" content=\"noindex, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MPGICO, Digital Advertising Solutions Lebanon,Web development Lebanon, Advertising and Marketing solutions Lebanon, web solutions Lebanon, SEO Lebanon, Mobile Applications Lebanon, Web development Qatar, Advertising and Marketing solutions Qatar, web solutions Qatar, SEO Doha Qatar, Mobile Applications Qatar\" \/>\n<meta property=\"og:description\" content=\"Digital Advertising Solutions Lebanon,Web development Lebanon, Advertising and Marketing solutions Lebanon, web solutions Lebanon, SEO Lebanon\" \/>\n<meta property=\"og:url\" content=\"https:\/\/mpgico.com\/new\/mysql\/\" \/>\n<meta property=\"og:site_name\" content=\"mpgico\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/mpgico.com\\\/new\\\/mysql\\\/\",\"url\":\"https:\\\/\\\/mpgico.com\\\/new\\\/mysql\\\/\",\"name\":\"MPGICO, Digital Advertising Solutions Lebanon,Web development Lebanon, Advertising and Marketing solutions Lebanon, web solutions Lebanon, SEO Lebanon, Mobile Applications Lebanon, Web development Qatar, Advertising and Marketing solutions Qatar, web solutions Qatar, SEO Doha Qatar, Mobile Applications Qatar\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/mpgico.com\\\/new\\\/#website\"},\"datePublished\":\"2019-10-25T11:13:14+00:00\",\"description\":\"Digital Advertising Solutions Lebanon,Web development Lebanon, Advertising and Marketing solutions Lebanon, web solutions Lebanon, SEO Lebanon\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/mpgico.com\\\/new\\\/mysql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/mpgico.com\\\/new\\\/mysql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/mpgico.com\\\/new\\\/mysql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/mpgico.com\\\/new\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Mysql\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/mpgico.com\\\/new\\\/#website\",\"url\":\"https:\\\/\\\/mpgico.com\\\/new\\\/\",\"name\":\"mpgico\",\"description\":\"mpgico\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/mpgico.com\\\/new\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MPGICO, Digital Advertising Solutions Lebanon,Web development Lebanon, Advertising and Marketing solutions Lebanon, web solutions Lebanon, SEO Lebanon, Mobile Applications Lebanon, Web development Qatar, Advertising and Marketing solutions Qatar, web solutions Qatar, SEO Doha Qatar, Mobile Applications Qatar","description":"Digital Advertising Solutions Lebanon,Web development Lebanon, Advertising and Marketing solutions Lebanon, web solutions Lebanon, SEO Lebanon","robots":{"index":"noindex","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"og_locale":"en_US","og_type":"article","og_title":"MPGICO, Digital Advertising Solutions Lebanon,Web development Lebanon, Advertising and Marketing solutions Lebanon, web solutions Lebanon, SEO Lebanon, Mobile Applications Lebanon, Web development Qatar, Advertising and Marketing solutions Qatar, web solutions Qatar, SEO Doha Qatar, Mobile Applications Qatar","og_description":"Digital Advertising Solutions Lebanon,Web development Lebanon, Advertising and Marketing solutions Lebanon, web solutions Lebanon, SEO Lebanon","og_url":"https:\/\/mpgico.com\/new\/mysql\/","og_site_name":"mpgico","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/mpgico.com\/new\/mysql\/","url":"https:\/\/mpgico.com\/new\/mysql\/","name":"MPGICO, Digital Advertising Solutions Lebanon,Web development Lebanon, Advertising and Marketing solutions Lebanon, web solutions Lebanon, SEO Lebanon, Mobile Applications Lebanon, Web development Qatar, Advertising and Marketing solutions Qatar, web solutions Qatar, SEO Doha Qatar, Mobile Applications Qatar","isPartOf":{"@id":"https:\/\/mpgico.com\/new\/#website"},"datePublished":"2019-10-25T11:13:14+00:00","description":"Digital Advertising Solutions Lebanon,Web development Lebanon, Advertising and Marketing solutions Lebanon, web solutions Lebanon, SEO Lebanon","breadcrumb":{"@id":"https:\/\/mpgico.com\/new\/mysql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/mpgico.com\/new\/mysql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/mpgico.com\/new\/mysql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/mpgico.com\/new\/"},{"@type":"ListItem","position":2,"name":"Mysql"}]},{"@type":"WebSite","@id":"https:\/\/mpgico.com\/new\/#website","url":"https:\/\/mpgico.com\/new\/","name":"mpgico","description":"mpgico","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/mpgico.com\/new\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/mpgico.com\/new\/wp-json\/wp\/v2\/pages\/10435","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mpgico.com\/new\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/mpgico.com\/new\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/mpgico.com\/new\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mpgico.com\/new\/wp-json\/wp\/v2\/comments?post=10435"}],"version-history":[{"count":1,"href":"https:\/\/mpgico.com\/new\/wp-json\/wp\/v2\/pages\/10435\/revisions"}],"predecessor-version":[{"id":10436,"href":"https:\/\/mpgico.com\/new\/wp-json\/wp\/v2\/pages\/10435\/revisions\/10436"}],"wp:attachment":[{"href":"https:\/\/mpgico.com\/new\/wp-json\/wp\/v2\/media?parent=10435"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}