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 |
+-----------------+-------+-------+

2 comments:

custom web design said...

Hey, you have a great blog here! I'm definitely going to bookmark you!
Cool Links:
custom website design
MCSE
web hosting provider
web site design
Data Conversion, Data Entry Service
Please Come and visit if you get time..
Thanks.

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. :)