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.
// 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
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.
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.