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

No comments: