I'm trying to make my mysql tables as fast as possible. There's no errors in them, but I see mixed feedback on whether people should do 'repair' before doing 'optimize'. Two websites and multiple users said repair can cause data loss. I have a book, and I can't take that risk to lose any rows. Other sites say it's 100% safe and will not affect it. I tested it on a duplicated table and no rows are removed, but I can't tell if any text is truncated or not from the 30,000 rows. So, do you know if it's 100% proven safe to repair a table that is not broken? If no rows or edits are ever made to it, should someone continue to do repair and optimize every two weeks, just from read-only use? Thanks!
You can periodically run optimize to clear out overhead, but otherwise shouldn't need to perform any other routine maintenance. Repair shouldn't be used unless the table has errors. I would not run any repair, or optimize unless you have a valid reason to. If you're concerned about errors, run check table first and then repair or optimize. The risks in data loss you speak of, are running repair on MyISAM tables, and not optimize. If something crashes the server during a repair operation, it can cause data loss. Optimize fails on a MyISAM table with errors but it shouldn't cause data loss itself. Bottom line is if you care about data integrity you wouldn't even consider MyISAM. Stick with InnoDB or XtraDB and tune it properly. It should be faster and more reliable than MyISAM.
if there is no overhead no need to run 'optimize' nor 'repair', if you see in phpMySql or some other manager 'overhed' then do both.