Tuesday, December 06, 2005

db4free.net data type statistics

In response to Beat's article about "Discover "bad" use of ENUM and SET types using MySQL information_schema", here's db4free's data type statistic:
mysql> SELECT    DATA_TYPE,
-> COUNT(*) AS n,
-> ROUND(COUNT(*)
-> / (SELECT COUNT(*) FROM information_schema.COLUMNS)
-> * 100, 2
-> ) AS `%`
-> FROM information_schema.COLUMNS
-> WHERE TABLE_SCHEMA NOT LIKE 'mysql'
-> GROUP BY DATA_TYPE
-> ORDER BY n DESC;
+-----------------+-------+-------+
| DATA_TYPE | n | % |
+-----------------+-------+-------+
| varchar | 69993 | 28.29 |
| int | 57613 | 23.29 |
| tinyint | 40502 | 16.37 |
| mediumint | 16413 | 6.63 |
| smallint | 16292 | 6.58 |
| text | 16011 | 6.47 |
| char | 14142 | 5.72 |
| datetime | 4297 | 1.74 |
| mediumtext | 2059 | 0.83 |
| enum | 1965 | 0.79 |
| decimal | 1811 | 0.73 |
| bigint | 1340 | 0.54 |
| tinytext | 809 | 0.33 |
| date | 802 | 0.32 |
| float | 720 | 0.29 |
| blob | 543 | 0.22 |
| longtext | 481 | 0.19 |
| double | 445 | 0.18 |
| timestamp | 348 | 0.14 |
| mediumblob | 166 | 0.07 |
| tinyblob | 122 | 0.05 |
| time | 116 | 0.05 |
| float unsigned | 104 | 0.04 |
| varbinary | 73 | 0.03 |
| set | 35 | 0.01 |
| longblob | 35 | 0.01 |
| double unsigned | 24 | 0.01 |
| bit | 5 | 0.00 |
| binary | 5 | 0.00 |
| year | 3 | 0.00 |
+-----------------+-------+-------+

1 comment:

Sheeri said...

Can you do more of an analysis, like Beat did? Obviously user data is confidential, but it would be great to see how many enums are 'good' (ie, static data like "yes" and "no" or continents) and how many are dynamic data, much like the bad examples I had. :)