Optimize table after every delete?

[Jaguar]

Is it a bad idea to do this?

 

DELETE FROM table WHERE id = $id;

OPTIMIZE TABLE table;

 

I'm guessing it could get nasty if the database were huge, but this database is small and only contains about 10,000 records. Also deletes shouldn't happen that often. But I'd like to know for future reference if I work with much larger and more active databases. Should I make a seperate button to optimize the tables so it doesn't optimize with every delete?

[Wildbug]

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, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.

 

This statement requires SELECT and INSERT privileges for the table.

 

In most setups, you need not run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable-length rows, it is not likely that you need to do this more than once a week or month and only on certain tables.

 

If you do have a db that needs to be OPTIMIZEd sometimes, one way to do it is to include a line in your deletion code along the lines of:

 

<?php

mysql_query("DELETE blah blah blah...");
if (mt_rand(0,100000) == 1) mysql_query("OPTIMIZE TABLE table");
?>

 

That way it'd only optimize, on average, every 100,000 deletes.

[fenway]

Is it a bad idea to do this?

 

DELETE FROM table WHERE id = $id;

OPTIMIZE TABLE table;

 

I'm guessing it could get nasty if the database were huge, but this database is small and only contains about 10,000 records. Also deletes shouldn't happen that often. But I'd like to know for future reference if I work with much larger and more active databases. Should I make a seperate button to optimize the tables so it doesn't optimize with every delete?

 

Why is this a problem for you?