Bok
01-16-2009, 02:04 PM
I found this mildly interesting..
mysql> SELECT concat(table_schema,'.',table_name),concat(round(table_rows/1000000,2),'M')
rows,concat(round(data_length/(1024*1024*1024),2),'G')DATA,concat(round(index_length/(1024*1024*1024),2),'G')
idx,concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size,round(index_length/data_length,2) idxfrac FROM information_schema.TABLES where table_schema =
'stats' ORDER BY data_length+index_length DESC LIMIT 20;
+-------------------------------------+---------+-------+-------+------------+---------+
| concat(table_schema,'.',table_name) | rows | DATA | idx | total_size | idxfrac |
+-------------------------------------+---------+-------+-------+------------+---------+
| stats.boinc_host_week | 163.33M | 8.97G | 3.71G | 12.69G | 0.41 |
| stats.boinc_user_week | 66.54M | 4.46G | 1.51G | 5.97G | 0.34 |
| stats.boinc_host | 6.12M | 4.61G | 0.83G | 5.44G | 0.18 |
| stats.boinc_combined_user_week | 43.46M | 2.95G | 0.78G | 3.74G | 0.27 |
| stats.boinc_host_month | 39.34M | 2.16G | 0.87G | 3.04G | 0.40 |
| stats.boinc_user | 2.46M | 1.67G | 0.68G | 2.34G | 0.41 |
| stats.user | 2.20M | 1.47G | 0.53G | 2.00G | 0.36 |
| stats.boinc_comb_milestone | 9.92M | 0.53G | 1.23G | 1.77G | 2.31 |
| stats.boinc_user_month | 16.03M | 1.07G | 0.36G | 1.43G | 0.33 |
| stats.nonboinc_user | 1.46M | 0.91G | 0.43G | 1.35G | 0.47 |
| stats.boinc_combined_user | 1.60M | 0.59G | 0.64G | 1.23G | 1.09 |
| stats.boinc_combined_user_month | 9.04M | 0.61G | 0.21G | 0.82G | 0.34 |
| stats.boinc_milestone | 3.76M | 0.24G | 0.46G | 0.71G | 1.91 |
| stats.boinc_combined_team_week | 2.28M | 0.29G | 0.03G | 0.33G | 0.12 |
| stats.boinc_team_week | 3.67M | 0.19G | 0.08G | 0.27G | 0.44 |
| stats.boinc_milestone_base | 1.37M | 0.08G | 0.10G | 0.18G | 1.32 |
| stats.nonboinc_milestone | 1.08M | 0.05G | 0.09G | 0.14G | 1.89 |
| stats.boinc_team | 0.13M | 0.08G | 0.02G | 0.10G | 0.20 |
| stats.boinc_combined_team_month | 0.55M | 0.07G | 0.01G | 0.08G | 0.15 |
| stats.boinc_team_month | 0.89M | 0.05G | 0.02G | 0.07G | 0.43 |
+-------------------------------------+---------+-------+-------+------------+---------+
20 rows in set (0.01 sec)
especially when two of those tables are superfluous... :)
mysql> SELECT concat(table_schema,'.',table_name),concat(round(table_rows/1000000,2),'M')
rows,concat(round(data_length/(1024*1024*1024),2),'G')DATA,concat(round(index_length/(1024*1024*1024),2),'G')
idx,concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size,round(index_length/data_length,2) idxfrac FROM information_schema.TABLES where table_schema =
'stats' ORDER BY data_length+index_length DESC LIMIT 20;
+-------------------------------------+---------+-------+-------+------------+---------+
| concat(table_schema,'.',table_name) | rows | DATA | idx | total_size | idxfrac |
+-------------------------------------+---------+-------+-------+------------+---------+
| stats.boinc_host_week | 163.33M | 8.97G | 3.71G | 12.69G | 0.41 |
| stats.boinc_user_week | 66.54M | 4.46G | 1.51G | 5.97G | 0.34 |
| stats.boinc_host | 6.12M | 4.61G | 0.83G | 5.44G | 0.18 |
| stats.boinc_combined_user_week | 43.46M | 2.95G | 0.78G | 3.74G | 0.27 |
| stats.boinc_host_month | 39.34M | 2.16G | 0.87G | 3.04G | 0.40 |
| stats.boinc_user | 2.46M | 1.67G | 0.68G | 2.34G | 0.41 |
| stats.user | 2.20M | 1.47G | 0.53G | 2.00G | 0.36 |
| stats.boinc_comb_milestone | 9.92M | 0.53G | 1.23G | 1.77G | 2.31 |
| stats.boinc_user_month | 16.03M | 1.07G | 0.36G | 1.43G | 0.33 |
| stats.nonboinc_user | 1.46M | 0.91G | 0.43G | 1.35G | 0.47 |
| stats.boinc_combined_user | 1.60M | 0.59G | 0.64G | 1.23G | 1.09 |
| stats.boinc_combined_user_month | 9.04M | 0.61G | 0.21G | 0.82G | 0.34 |
| stats.boinc_milestone | 3.76M | 0.24G | 0.46G | 0.71G | 1.91 |
| stats.boinc_combined_team_week | 2.28M | 0.29G | 0.03G | 0.33G | 0.12 |
| stats.boinc_team_week | 3.67M | 0.19G | 0.08G | 0.27G | 0.44 |
| stats.boinc_milestone_base | 1.37M | 0.08G | 0.10G | 0.18G | 1.32 |
| stats.nonboinc_milestone | 1.08M | 0.05G | 0.09G | 0.14G | 1.89 |
| stats.boinc_team | 0.13M | 0.08G | 0.02G | 0.10G | 0.20 |
| stats.boinc_combined_team_month | 0.55M | 0.07G | 0.01G | 0.08G | 0.15 |
| stats.boinc_team_month | 0.89M | 0.05G | 0.02G | 0.07G | 0.43 |
+-------------------------------------+---------+-------+-------+------------+---------+
20 rows in set (0.01 sec)
especially when two of those tables are superfluous... :)