How to determine the size of a set of rows based on a where clause

[Manish Pandit]

Hi,

I have tried looking for a solution to this problem but no luck. The
thing is, in my system, I have a set of users who have a 'quota'
limiting their usage. They can create n number of tables and put m
number of rows in it, but at the end, their 'usage' can not exceed,
lets say, x MB. My problem is, how to I evaluate the space being used,
given the fact that I can pull all data for a user by including a user
ID in the where clause. In other words, I need to find size of the
result set containing all user's rows.

Please let me know. I am using mysql jdbc driver and not sure if there
is any API that deals with the physical size of the resultset.
resultset.getFetchSize() talks in terms of rows v/s the actual space
they take.

-cheers,
Manish

[Gordon Burditt]

>I have tried looking for a solution to this problem but no luck. The

thing is, in my system, I have a set of users who have a 'quota'
limiting their usage. They can create n number of tables and put m
number of rows in it, but at the end, their 'usage' can not exceed,
lets say, x MB. My problem is, how to I evaluate the space being used,
given the fact that I can pull all data for a user by including a user
ID in the where clause. In other words, I need to find size of the
result set containing all user's rows.

If each table is owned by a user, and therefore the user gets dinged
for the space occupied by said table, then SHOW TABLE STATUS can
provide interesting data like the size of records in the data file,
the average size per row, and the index length. If a table contains
a jumble of records owned by different users, this is less useful.

SHOW TABLE STATUS is also useful in case the Row_format is Fixed, in
which case the record size is the fixed size times the number of records
owned by this user, and I guess you could apportion the index size
proportionately to the number of records. If the Row_format is Dynamic,
I guess you could use the average row size times the number of rows,
but for text blobs this may not be very accurate.

Gordon L. Burditt

[Manish Pandit]

Thanks for your reply..but in my case, data of multiple users is spread
across a single table, demarked by a userID field.

I will however use the show table status for another requirement that I
have.

-cheers,
Manish