By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,784 Members | 1,182 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,784 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Oct 17 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
>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
Oct 17 '05 #2

P: n/a
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

Oct 17 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.