MySQL - How to measure the amount of space used by some rows?

Question

I am working on a project management program built on the LAMP stack. I previously decided not to provide each of my clients with my own database, but rather to combine all the business data into one large database.

The first column in all database tables is business_id. This line indicates to which company a certain part of the data belongs, which does not allow us to expose the data of Business 1 in Business 2.

But since I'm trying to smooth out billing excesses, it would be very useful to be able to run a cron job once a month to determine how much database space each business uses. So, here is my question: is there a way to get MySQL to return the number of bytes used by all lines of WHERE business_id = 'x'?

Answer 1

Since you are likely to pay only for the information that the customer explicitly pays you for storage, simply summarize the lengths of the fields that they explicitly give you. (Omit the internal fields such as business_id , service_level_id , permissions or something else.) Then refer to the storage requirements and calculate.

So, where B is the number of bytes required for all number fields, and there are 3 text fields txtA, txtB and txtC, for example:

 select business_id, count(*) * B + sum(length(txtA) + length(txtB) + length(txtC)) as bytes from table where business_id={id}; 

But, as soon as you have enough data on your system, I suspect the request will be quite lengthy. It might be easier to simply tell your customers that you expose them to their “approximate” data warehouse. Use average_row_length * count(*) your lines and average_row_length * count(*) bit to account for your overhead.

Answer 2

Hope this helps:

 SELECT table_schema, count(*) TABLES, concat(round(sum(table_rows)/1000000,2),'M') rows,concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES group by table_schema;