prevent mysql MyISAM table crashes

I've been having problems with a database repeatedly having crashed tables. Luckily it's easy enough to fix by repairing the crashed table, but it's hardly good practice to have to watch for the table to crash (or have the client tell me) and then fix it. The crashes tend to occur after a change has been made to the database which the client has access to via a CMS.

I noticed the most recent time the table crashed that it referenced a number - som开发者_StackOverflowething like found 57 of 89; which I then noticed in the Cardinality for the primary key. Putting 2 and 2 together I googled cardinality and found that Optimising the table was in some way related and thus I thought that by optimising the table regularly, like after an update, it would help prevent crashes. Is this true or have I managed to get 73 rather than 4?

I can send MYSQL functions to the database when the client makes changes via PHP, so help from that perspective would be great.

Any other help with table crashes would be greatly appreciated.

is NOT true optimize table will prevent the table for crashing

in fact, U SHOULD NOT run optimize table too often (table get locked) although is designed to compact free data

try mysqlcheck -C

or mysqlcheck -c

MyISAM table usually gets corrupted for the following reasons:

• A bug in MySQL or external issues (OS crash, hard power-down,out-of-memory OS kill) that causes mysqld to exit abnormally
• A bug in the MyISAM storage engine
• Running two instances of mysqld on the same data at the same time
• Hardware malfunction

So the best you can do to avoid corruption is to have a good UPS, run most stable versions of OS and MySQL, make sure your hardware is functioning properly, make sure you have adequate amount of RAM (to avoid OOM kills, and to avoid tempting the devil in general, bugs are often hit in out-of-memory conditions), and be a good boy - do not kill mysqld with signal 9, and when restarting make sure the old instance of mysqld has gone down before you start the new one.

You may also want to take measures to deal with the corruption. Back up your data frequently, and keep the tables reasonably small to avoid lengthy recovery times.

Using InnoDB is another option which is in vogue today as it aligns better with the database theory taught in schools but it does have problems of its own and may introduce new issues if you try to migrate - data bloat, slower performance, deadlocks, data corruption (InnoDB style), more complex - more likely to hit a bug and harder to troubleshoot, etc.