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.


    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,
    order by columns desc;


    • 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.


    • 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%.

    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.