Optimize

[DexterII]

Maybe add an option in the admin cp to automatically optimize all the tables

ex

OPTIMIZE TABLE `subscribethread`

Or is this added already?

[leadZERO]

OPTIMIZE TABLE is better used on tables with a lot of variable length records that can change size. I imagine tables like subscribethread use all fixed-length fields.

A better case would be on post. However, if you look on the searchable comments pages at mysql.com (http://www.mysql.com/doc/O/P/OPTIMIZE_TABLE.html) you will see it can be very dangerous to use this command on large tables. I would recommend not adding it until the implementation is a little more bullet-proof.

[DexterII]

Something like the session table gets a very large overhead even after an hour.

[eva2000]

Originally posted by DexterII
Something like the session table gets a very large overhead even after an hour. OPTIMIZE only works on MyISAM and BDB tables - sessions table is HEAP format

[leadZERO]

Originally posted by DexterII
Something like the session table gets a very large overhead even after an hour.

mysql> describe session;
sessionhash char
userid int unsigned
host char
useragent char
lastactivity int unsigned
styleid smallint unsigned
location char

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the datafile.

Session is made entirely of fixed-length rows. Again, like I said, OPTIMIZE TABLE wouldn't work for it even if it was the right table type.

[Wayne Luke]

You can repair and optimize tables from the Admin Control Panel in vb3.

[Raz Meister]

Thats a good idea, but personally I would be wary about going into the AdminCP if I suspected a broken board.

A good SSH/myismchk for me :D

[tubedogg]

If you have a broken board you do not use OPTIMIZE to repair it, you use REPAIR.

[Raz Meister]

Obviously ;)

[tubedogg]

Originally posted by Razzie
Obviously ;) Then what was the point of your comment? :p

[Raz Meister]

Actually I was going to say that to you. No one said anything about optimizing a broken board ;)