I want to find the space occupied by the records in database. I have 2000 records. I need to find how much space occupied by empid 4 in the database in mySQL.
Please let me know the query in mySQL.
A:
SHOW TABLE STATUS is the command you're looking for:
SHOW TABLE STATUS IN your_database LIKE 'yourtable';
The metric you're looking for may be either Data_length or Avg_row_length.
Piskvor
2010-09-20 11:34:16
thanks for your quick response sir..which attribute would give me the desired result. suppose i have 10000 records with empid=4 . with which property i can get the space occupied by that 10000 records. Data_length? or Avg_row_length ? please clarify me...
2010-09-20 12:09:43
@user294146: I think that you can not get information about specific rows in a table; the statistics are about the whole table.
Piskvor
2010-09-20 12:15:35
SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr] can i add where condition from the above syntax.
2010-09-20 12:17:26
@user294146: No, you can not, it will not be a valid query and will throw an error. From the linked documentation: "The WHERE clause can be given to select rows using more general conditions, as discussed in Section 20.28, “Extensions to SHOW Statements”." http://dev.mysql.com/doc/refman/5.1/en/extended-show.html That page, in turn, says that you can use the `where` to filter on table properties, e.g. `SHOW TABLE STATUS IN yourdatabase WHERE Engine="MyISAM"`; again, you can not get information about specific rows in a table; the statistics are about the whole table.
Piskvor
2010-09-20 12:24:34