table size ?

[Anon]

I had a table with 5500 rows and 2.5mb. I reduced the table to 1734 and mysql still reports 2.5mb. Why is this?

I posted this on mySQL specific boards, but haven't gotten a response. I'd like to reduce the storage size of my database, and this table in particular.

Thanks.

Ron Phillips
net.i.dev

[[deleted]]

You need to 'optimize' the tables;
http://www.mysql.com/doc/O/P/OPTIMIZE_TABLE.html

[brandonschnell]

general database info:

databases don't reuse space unless absolutely necessary (or you request it using an optimization command)

it helps for performance. most databases will maintain small (~2-4k) pages (mysql uses a linked list) for the actual data and try to keep the pages about 50-75% full. with variable row lengths it takes time to find a page with enough free space. its more efficient for a database to ignore pages with deleted rows until the pages have a certain percentage free to guarantee its big enough for the row. optimizing the database will rearrange the pages releasing back to the OS any space that no longer is needed and help maintain a certain amount of space in each page.

http://www.mysql.com/doc/O/P/OPTIMIZE_TABLE.html