Changing tables from INNOBD to MyISAM storage

Awestruck

I have a small forum that uses INNODB storage for two tables. I now need to add a full text search facility and this means changing the two tables to MyISAM storage. I see that in phpMyAdmin this can be done quite easily Are there any precautions I should consider before converting the tables. I am worried that I might trash my database.

bradgrafelman

To my knowledge, MyISAM still doesn't support foreign key constraints, so it's possible that the forum utilizes those and depends on their functionality. For example, it might utilize "ON DELETE CASCADE" constraints to automatically delete records from one table when the row with the corresponding key is deleted from another table. I also believe MyISAM uses table-level locking while InnoDB supports more granular locking mechanisms.

Note that this is by no means an exhaustive collection of considerations; I certainly don't consider myself a DBMS guru/wizard.

Weedpacket

An alternative to changing the tables to use a less-robust engine would be to upgrade the dbms - the current version of MySQL does support full-text searching on InnoDB tables.

Bonesnap

Unless something has changed, MyISAM doesn't support transactions. From the reading I have done on the subject, MyISAM is more geared towards a "read heavy" scenario whereas InnoDB is more for lots of inserts and updates. And as brad has mentioned, MyISAM does table-level locks whereas InnoDB does row-level lock.

Awestruck

Thank you for your replies. I have discovered that there is no problem converting tables in phpMyAdmin as long as you have not changed collations or decreased column size. As a precaution, always backup your tables using phpMYAdmin’s Export facility.

The good news is that if you have MySQL version 5.6.4 or later, the INNODB storage engine allows full text searches. For the latest information see the MySQL website http://dev/mysql.com/doc/refman/5.6/en/fulltext-search.html. Can we infer that MyISAM is being phased out?

bradgrafelman

Awestruck;11031041 wrote:
I have discovered that there is no problem converting tables in phpMyAdmin

... except at least all of the potential issues already mentioned above...

Bonesnap

Awestruck;11031041 wrote:
Can we infer that MyISAM is being phased out?

I would not infer that, especially based on InnoDB supporting full text searching. That's not to say that's Oracle's ultimate goal, but for now MyISAM still has its place.