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 |
+-----------------+-------+-------+
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:
Subscribe to:
Post Comments (Atom)
1 comment:
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. :)
Post a Comment