No Of Bytes consumed of the resultants rows from a query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

No Of Bytes consumed of the resultants rows from a query

Post by anjanesh »

Is there anyway to find the space usage (in bytes or whatever) for a query say :
SELECT * FROM `Private_Messages` WHERE UserID='10'
I would like to show the user how much disk space he has used up. But this won't be possible using file methods since I dont have access to MySQL files and moreover there are many rows in one table where all the msgs are stored and I need to get only the ones of a particular user.
Its there in phpMyAdmin, but I couldnt trace out the function/query to get this result.

Code: Select all

// In the main frame, at startup...
$cfgї'ShowStats']             = TRUE;   // allow to display statistics and space usage in
                                        // the pages about database details and table
                                        // properties
Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

we've talked about this before. I, myself posted how phpMyAdmin calculates the information, however it is misleading as to how much space is actually consumed because the information is compressed by the database.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

I searched phpMyAdmin with feyd and came across this :
viewtopic.php?t=29024

SHOW TABLE STATUS LIKE 'table_name' displays table info.
How do I get the size of particular rows only ?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

read the MySQL docs on what space requirements are needed for each datatype used, create an information map based on those types, run through the table structure, adding up the size of each column of the rows specified, adding a byte here and there as needed by the space requirements.

Do remember that the information you recieve will be the uncompressed size, and not the actual space requirements. You may want to look into the compression methods used in the tables and max an average or estimate as a multiplier, if you want the most accuracy.
Post Reply