A set of useful queries on INFORMATION_SCHEMA follows. These queries can be used when approaching a new database, to learn about some of its properties, or they can be regularly used on an existing schema, so as to verify its integrity.
I will present queries for:
- Checking on database engines and size
- Locating duplicate and redundant indexes
- Checking on character sets for columns and tables, looking for variances
- Checking on processes and long queries (only with MySQL 5.1)
The following query returns the total size per engine per database. For example, it is common that in a given database, all …[Read more...]