* Juan Nin > I want to get amount of disk space used by certain rows on disk, > not a whole table, but only some rows. Is this possible?
How accurate does it have to be? On the byte? On the K? > For example I got a table like: > > CREATE TABLE `categories` ( > `id` int(11) NOT NULL auto_increment, > `name` varchar(50) NOT NULL default '', > `parent_category_id` int(11) NOT NULL default '0', > PRIMARY KEY (`id`), > UNIQUE KEY `name_parent` (`name`,`parent_category_id`) > ) TYPE=MyISAM; > > and I want to know the amount of disk space that is used by the > rows returned by certain query like: > > SELECT * FROM categories WHERE name LIKE 'a%'; Because your record consists of two integers and a variable length string and the table type is MyISAM, the record length is afaik 3+1+1+4+4+length(name). This query should give you the number of bytes occupied by these rows in the MYD file: SELECT SUM(3+1+1+4+4+length(name)) FROM categories WHERE name LIKE 'a%'; There is a 6 byte extra penalty for a row if the name has grown, i.e. been UPDATEd from the original inserted value to a _longer_ string value... <URL: http://www.mysql.com/doc/en/Dynamic_format.html > In addition there is the index... I don't know how to calculate the size of index entries in the MYI file. The integer primary key is probably easy to calculate, but the variable length name_parent index? Maybe you could use a factor based on the total MYD size compared to the total MYI size? [...] > and I got tables with files stored in BLOBs... Do you want to know the size of the blobs matching some WHERE clause without actually selecting them, or even selecting LENGTH(blob)? I don't think that can be done. But there is hopefully no index on the blob, so getting the size using LENGTH() should work: SELECT file,LENGTH(file) AS filesize FROM files WHERE name = 'config.sys' -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
