What Is the Database information_schema on phpMyAdmin?

Introduction

When working with MySQL databases through phpMyAdmin, you’ll encounter a default database named information_schema. This database is pivotal for understanding the internal workings of your MySQL server, providing access to essential metadata about the server’s configuration, structure, and operation.

What is information_schema?

The information_schema database is a virtual, read-only database containing metadata about all other databases managed by the MySQL server. Metadata is data about data, such as database names, table names, column data types, and user privileges. This database offers a standardized method to access comprehensive information about the databases you manage.

Why is information_schema Important?

1. Metadata Access: Provides a unified way to retrieve metadata about databases, tables, columns, indexes, and more.

2. Security and Permissions: Helps administrators understand and manage user privileges.

3. Database Maintenance: Offers insights into table sizes, index usage, and other statistics crucial for database optimization and maintenance.

4. Cross-Database Queries: Facilitates queries across multiple databases on the server, aiding in reporting and analysis.

Key Tables in information_schema

Here are some of the critical tables within the information_schema database and what they contain:

1. SCHEMATA

• Purpose: Contains information about all databases on the server.

• Key Columns:

CATALOG_NAME: The name of the catalog (always 'def' in MySQL).

SCHEMA_NAME: The name of the database (schema).

DEFAULT_CHARACTER_SET_NAME: The default character set for the database.

DEFAULT_COLLATION_NAME: The default collation for the database.

SQL_PATH: Always NULL, reserved for future use.

2. TABLES

• Purpose: Contains information about all tables in the databases.

• Key Columns:

TABLE_CATALOG: The name of the catalog.

TABLE_SCHEMA: The name of the database (schema) the table belongs to.

TABLE_NAME: The name of the table.

TABLE_TYPE: The type of the table (e.g., 'BASE TABLE' or 'VIEW').

ENGINE: The storage engine for the table.

VERSION: The version number of the table's row format.

ROW_FORMAT: The row-storage format (e.g., 'Compact').

TABLE_ROWS: The number of rows in the table.

AVG_ROW_LENGTH: The average row length.

DATA_LENGTH: The length of the table's data file.

INDEX_LENGTH: The length of the table's index file.

DATA_FREE: The amount of allocated but unused space.

AUTO_INCREMENT: The next auto-increment value.

CREATE_TIME: When the table was created.

UPDATE_TIME: When the table was last updated.

CHECK_TIME: When the table was last checked.

TABLE_COLLATION: The table's default collation.

CHECKSUM: The table's checksum value.

CREATE_OPTIONS: Extra options used in table creation.

TABLE_COMMENT: Comments about the table.

3. COLUMNS

• Purpose: Contains information about columns in all tables.

• Key Columns:

TABLE_CATALOG: The name of the catalog.

TABLE_SCHEMA: The name of the database (schema) the table belongs to.

TABLE_NAME: The name of the table.

COLUMN_NAME: The name of the column.

ORDINAL_POSITION: The position of the column in the table.

COLUMN_DEFAULT: The default value of the column.

IS_NULLABLE: Whether the column can contain NULL values.

DATA_TYPE: The data type of the column.

CHARACTER_MAXIMUM_LENGTH: The maximum length of the column (for string types).

CHARACTER_OCTET_LENGTH: The maximum length in bytes (for string types).

NUMERIC_PRECISION: The numeric precision (for numeric types).

NUMERIC_SCALE: The numeric scale (for numeric types).

CHARACTER_SET_NAME: The character set name (for string types).

COLLATION_NAME: The collation name (for string types).

COLUMN_TYPE: The column data type, as used in the CREATE TABLE statement.

COLUMN_KEY: Indicates whether the column is indexed.

EXTRA: Additional information (e.g., 'auto_increment').

PRIVILEGES: Privileges associated with the column.

COLUMN_COMMENT: Comments about the column.

GENERATION_EXPRESSION: The expression for generated columns.

4. STATISTICS

• Purpose: Contains information about table indexes.

• Key Columns:

TABLE_CATALOG: The name of the catalog.

TABLE_SCHEMA: The name of the database (schema) the table belongs to.

TABLE_NAME: The name of the table.

NON_UNIQUE: Whether the index is unique.

INDEX_SCHEMA: The name of the database (schema) the index belongs to.

INDEX_NAME: The name of the index.

SEQ_IN_INDEX: The column sequence number in the index.

COLUMN_NAME: The name of the column.

COLLATION: How the column is sorted in the index.

CARDINALITY: The estimated number of unique values in the index.

SUB_PART: The number of indexed characters (for string types).

PACKED: Whether the index is packed.

NULLABLE: Whether the column can contain NULL values.

INDEX_TYPE: The index method used.

COMMENT: Comments about the index.

INDEX_COMMENT: Comments about the index.

5. USER_PRIVILEGES

• Purpose: Contains information about global privileges.

• Key Columns:

GRANTEE: The user who has the privilege.

TABLE_CATALOG: The name of the catalog.

PRIVILEGE_TYPE: The type of privilege (e.g., SELECT, INSERT).

IS_GRANTABLE: Whether the privilege is grantable to other users.

How to Use information_schema in phpMyAdmin

phpMyAdmin provides a user-friendly interface to interact with the information_schema database. Here’s how you can leverage it:

1. Viewing Metadata: Navigate to the information_schema database within phpMyAdmin to explore its tables. You can view the metadata by selecting the relevant table and browsing its rows.

Running Queries: Use the SQL tab in phpMyAdmin to write custom queries against the information_schema tables. For instance, to list all tables in a specific database:

SELECT TABLE_NAME

FROM information_schema.TABLES

WHERE TABLE_SCHEMA = 'your_database_name';

2. Analyzing Performance: Check table sizes and index usage to identify potential performance bottlenecks:

SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH

FROM information_schema.TABLES

WHERE TABLE_SCHEMA = 'your_database_name';

3. Security Audits: Review user privileges to ensure proper security settings:

SELECT *

FROM information_schema.USER_PRIVILEGES;

4. Example Queries Using information_schema

Here are some practical examples of queries you might run using information_schema:

1. List All Databases:

SELECT SCHEMA_NAME

FROM information_schema.SCHEMATA;

2. List All Tables in a Database:

SELECT TABLE_NAME

FROM information_schema.TABLES

WHERE TABLE_SCHEMA = 'your_database_name';

3. Get Column Details for a Table:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT

FROM information_schema.COLUMNS

WHERE TABLE_SCHEMA = 'your_database_name'

AND TABLE_NAME = 'your_table_name';

4. Find Indexes on a Table: SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE, INDEX_TYPE

FROM information_schema.STATISTICS

WHERE TABLE_SCHEMA = 'your_database_name'

AND TABLE_NAME = 'your_table_name';

5. Check User Privileges: SELECT *

FROM information_schema.USER_PRIVILEGES

WHERE GRANTEE = "'your_username'@'your_host'";

Conclusion

The information_schema database is an invaluable resource for anyone managing MySQL databases, especially through phpMyAdmin. It provides detailed metadata that can help with everything from basic database management to advanced performance tuning and security audits. By understanding and utilizing the information_schema database, you can gain deeper insights into the structure and operations of your MySQL server, leading to more efficient and effective database administration.