Find most used data type in MySQL database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-02-18

Table of Contents:


    The query below returns data types used in a database (schema) ordered by the number of their occurrence.

    Query

    select data_type,
           count(*) as columns,
           cast(100*count(*)/sum_all.columns as decimal(36,2)) 
                as percent_columns,
           count(distinct concat(col.table_schema, '.', col.table_name))
                as tables,
           cast(100*count(distinct concat(col.table_schema,'.',col.table_name)) 
                / sum_all.tables as decimal(36,2)) as percent_tables
    from information_schema.columns col
    join (select count(distinct concat(c.table_schema, '.', c.table_name))
                        as tables,
                 count(*) as columns
          from information_schema.columns c
          join information_schema.tables t
               on c.table_schema = t.table_schema
               and c.table_name = t.table_name
          where t.table_schema not in ('information_schema', 'mysql',
                                       'performance_schema', 'sys')
                and t.table_type = 'BASE TABLE'
          ) sum_all on true
    join information_schema.tables tab
         on col.table_schema = tab.table_schema
         and col.table_name = tab.table_name
    where tab.table_schema not in ('information_schema', 'mysql',
                                   'performance_schema', 'sys')
          and tab.table_type = 'BASE TABLE'
    group by data_type,
             sum_all.columns,
             sum_all.tables
    order by columns desc;
    

    Columns

    • data_type - built-in or user data type without length or precision, e.g. int, varchar or datetime
    • columns - number of columns in a database (schema) with this data type
    • percent_columns - percentage of columns with this data type. The rows total 100%
    • tables - number of tables in a database (schema) with this data type
    • percent_tables - percentage of tables with columns that have this data type.

    Rows

    • One row represents one data type
    • Scope of rows: all data types used in a database (schema)
    • Ordered by number of columns in descending order

    Sample results

    Sample results for the Sakila database (schema). As you can see most popular data type is smallint and varchar - both are used in 20 columns which is 22.5% of all columns in 16 tables.

    Note that smallint data type is used in 14 tables that represent 87.5% of all tables, whereas varchar data type is used in 9 tables which is 56.3%.

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept