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

Question

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

The first column in all database tables is business_id. This row indicates which business a certain piece of data belongs to, which keeps us from exposing Business 1's data to Business 2.

But as I try to iron out the billing kinks, it would be very useful to be able to run a cron job once a month to determine how much database space each business is using. So here's my question: Is there a way to get MySQL to return the number of bytes used by all rows WHERE business_id = 'x'?

Answer 1

Since you will probably only be billing for the information the customer is explicitly paying you to store, just sum up the lengths of the fields they explicitly give you. (Omit internal fields like business_id, service_level_id, permissions, or whatever.) Then, refer to the storage requirements and calculate.

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

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

But, once you've got a reasonable amount of data in your system, I suspect that query is going to be pretty long-running. It might be simpler to just tell your clients you'll be billing them on their "approximate" data storage. Use the average_row_length * count(*) of their rows and trim a little off to account for your overhead.