SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    myISAM to innoDB conversion

    Hi

    I have existing mySql database which now use myISAM engine. I lately start to experience some performance issues, due the large numbers of concurrent users on the website, and I would like to switch the database to innoDB engine.

    Database has about 35 tables full with data.
    My Questions are:

    1. If i go to phpMyAdmin, and change all the tables from myISAM to innoDB, can I cause some mess in the existing data?

    2. I read somewhere that myISAM lock the tables and excecute the update queries one by one, and that innoDB does not use locks. Is that true?

    3. If I use the innoDB, will those update queries be performed faster?

    4. If i add indexes on columns , that I frequently use in my select and update queries, will those updates and searches be performed faster with innoDB compared with myISAM?

    5. I have primary keys in all tables. Will those primary keys continue to work after I switch the engine, or I have to re-create them.

    Any links where I can read how can I perform all those changes trough phpMyAdmin will be deeply appreciated.

    Regards, Zoreli

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    1. If i go to phpMyAdmin, and change all the tables from myISAM to innoDB, can I cause some mess in the existing data?

    no

    2. I read somewhere that myISAM lock the tables and excecute the update queries one by one, and that innoDB does not use locks. Is that true?

    not quite true -- myisam locks the entire table, but innodb locks only the row(s) affected

    3. If I use the innoDB, will those update queries be performed faster?

    yup

    4. If i add indexes on columns , that I frequently use in my select and update queries, will those updates and searches be performed faster with innoDB compared with myISAM?

    searches no, updates yes

    5. I have primary keys in all tables. Will those primary keys continue to work after I switch the engine,

    yup

    Any links where I can read how can I perform all those changes trough phpMyAdmin will be deeply appreciated.

    link not necessary, just run this query in the SQL tab --
    Code:
    ALTER TABLE foo ENGINE=innodb
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937

    Thanks a lot for sharing your knowledge and spending your time to answer my questions.

    Regards, Zoreli


Bookmarks

Posting Permissions

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