CodeIgniter Forums

Full Version: Get size that certain rows are taking up in table (with completely different content)
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Im creating an app thats mysql based, you can keep track of different 'assets' which reside in their own tables, and the columns and the data/datatypes are completely dynamic, one asset table can have 20 columns all LONGTEXT, the other can have 2 columns that are BOOLEAN or something.

When you change anything in the assets, it creates a revision in a separate revisions table, which has "revision_id, asset_id, table, date, account_id, data", the data is the content of the new row data in a serialized array.

My question is, if i wanted to get the disk space that a specific asset is taking up, (all its revisions and the asset ID in the table itself), whats the best way to do that? I know that you cant just get the row size, you can get the average row size in a table via 'show table status', but if 100 revision is from an asset_table that has 20 larger sized columns, and the other 100 are from an asset_table thats all boolean, then the average size will be completely off..

Heres another thing, the asset_tables are completely dynamic, you can change/add/delete columns, as well as their types. So you can have an asset with a revision (of its 20 LONGTEXT columns), then change the asset_table and the next 100 revisions will be all smaller/fewer columns.

What I was thinking, is at the time the revision is created, you get the average row size from the asset_table that its in, and store that with the revision in the revisions table. Then you can total up all the results in that column for whatever asset ID. I know thats not very percise, but its more accurate than getting the average row size from the revisions table (which will almost always be way off), or the average row size from the assets_table at run time (since it can be changed frequently, or never)