optimize db

[crawfd]

let's say you have a blogging site, ..and you have many users, ...over 500. When is the best time to implement an optimize table function? I was thinking of implementing this function after a user would decide to delete his account, and upon this action many rows in my db would be deleted. Is this even necessary? or is this a good idea for keeping the db organized and well maintained?.............

[vaaaska]

Wow, over 500, that's pretty nice.

I'm no expert by any means, but why would you allow a user to delete their account? Wouldn't it be more prudent when they "deleted" their account to just create a trigger to turn their site "off" and have a note sent to you that says they are doing so?

I've noticed on LiveJournal that people can delete an account but they have 30 days to undo it and turn it back on.

With that many users, I think a monthly optimization is probably not a bad idea either.

v

[freebie]

I agree. Best not to delete right away.
Also, you might also look into Foreign Key constraints (most databases support this, inlcuding MySQL InnoDB tables).
The idea is that if you delete a row in the parent table, all rows in child tables that are linked to that row in the parent table will also be deleted automatically (if the foreign key constraint is set to ON DELETE CASCADE).

[Weedpacket]

Rather than completely deleting the user's account, or merely setting some "active/inactive" field to "inactive", you could maintain a table of active users, and a table of inactive users. Move records from the active table to the inactive table. Depending on your dbms, you could write a trigger so that when you delete a user from the active table, a corresponding record is inserted in the inactive table.

Or you could keep the "active/inactive" field, and write a view of active users defined as "SELECT * FROM users WHERE active=true". Remember to index the active field, and if your DBMS can move records around internally so that all the active records are physically grouped together on the disk, that would be a big win.