Changing tables from INNOBD to MyISAM storage
Results 1 to 7 of 7

Thread: Changing tables from INNOBD to MyISAM storage

  1. #1
    Silver surfer
    Join Date
    Nov 2007
    Posts
    78

    Changing tables from INNOBD to MyISAM storage

    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.

  2. #2
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,395
    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.

  3. #3
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,843
    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.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  4. #4
    Senior Member
    Join Date
    Mar 2009
    Posts
    802
    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.
    Declare variables, not war.

  5. #5
    Silver surfer
    Join Date
    Nov 2007
    Posts
    78
    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/...xt-search.html. Can we infer that MyISAM is being phased out?

  6. #6
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,395
    Quote Originally Posted by Awestruck View Post
    I have discovered that there is no problem converting tables in phpMyAdmin
    ... except at least all of the potential issues already mentioned above...

  7. #7
    Senior Member
    Join Date
    Mar 2009
    Posts
    802
    Quote Originally Posted by Awestruck View Post
    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.
    Declare variables, not war.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •