MySQL – information_schema.statistics

Background

As a quick follow-up to our discussion of metadata on tables, let us discuss indexes.

BTW, the discussion on tables is here.

INFORMATION_SCHEMA.STATISTICS

Code


select

		tblStat.TABLE_NAME as 'table'

	  , tblStat.INDEX_NAME as 'index'

	  , case tblStat.NON_UNIQUE
			   when 0 then 'No'
			   when 1 then 'Yes'
			   else '?'
		end as 'Unique'


	  , GROUP_CONCAT( COLUMN_NAME order by SEQ_IN_INDEX ) as colList


	  , tblStat.CARDINALITY


from  INFORMATION_SCHEMA.STATISTICS tblStat


where  tblStat.TABLE_SCHEMA = 'drupal'


group by

		 tblStat.TABLE_SCHEMA
	   , tblStat.TABLE_NAME
	   , tblStat.INDEX_NAME


order by

		 tblStat.TABLE_SCHEMA
	  ,  tblStat.TABLE_NAME
	  ,  tblStat.INDEX_NAME


LIMIT 25

;


Output

 

Comparison with Other Databases

  1. GROUP_CONCAT
    • The group_concat is very, very powerful and has few equal in other database platforms
    • Function Documentation

 

SHOW INDEX

To get a bit more information, specifically cardinality ( Number of unique values in each column combination ), we can issue “show index” against the table.

Code

Syntax


show index from [table] from [database];

Sample


show index from search_index from drupal;

Output

References

  1. Reference
    • INFORMATION_SCHEMA Tables
      • INFORMATION_SCHEMA STATISTICS Table
    • Show Index
    •  Group_Function
      • Group By Function
        Link
    • Group_Concat

MySQL – information_schema.tables

Background

With SQL being a lingua-franca for quering data it should not be a far stretch to note that the various governing bodies have agreed on standards on how to expose dictionary.

 

Mysql

For instance, if we want to see which tables are in a database we can launch our favorite GUI SQL Editor or just access MySQL’s Client query tool eponymous named mysql.

 

information_schema.tables

SQL

Sample


select 
       TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
     , TABLE_TYPE, ENGINE
     , TABLE_ROWS, AVG_ROW_LENGTH 

from information_schema.tables 

where TABLE_TYPE not in ("SYSTEM VIEW")  

LIMIT 10


 

Output

Explanation

  1. Database
    • def
  2. Schema
    • audit_repository
  3. Table
  4. Table Type
    • Available Options
      • SYSTEM VIEW
      • BASE TABLE
  5. Engine
    • Available Options
      • Memory – System Tables
      • InnoDB
      • MyISAM
  6. TABLE ROWS
    • Number of records in table
  7. AVG ROW LENGTH
    • Based on column data type and used size allocation

 

Comparison with Other Databases

SQL Server

  1. Able to use double-quotes in query
    • We are able to use double-quotes (“) to delimit column values
  2. Limit N
    • In Transact SQL we use top N.
    • On the other hand, in MySQL we use LIMIT N

 

show tables

Objective

List tables and views that fits specified name format.

SQL

Syntax


show full tables from [database] like [name] where TABLE_TYPE in [BASE_TABLE | VIEW ];

Sample


show full tables from drupal like 'role%';

 

Output

 

describe

Objective

Display table’s structure.

SQL

Syntax


describe [table];

Sample


describe drupal.role;

Output

 

References

  1. MySQL
    • Show Tables
      • Show Tables Syntax
        Link
    • Show Commands
      • Extensions to SHOW Statements
        Link