MyISAM and InnoDB is a storage engine of MySQL. If you have large databases and have lots of traffic on your website, you will probably encounter a CPU load problem due to table locking features of MyISAM.

I created this article to share my problem on several website that I owned including sourcecodester.com. Almost every day I am worried about the load that MySQL is generating on my server’s CPU. In fact, there are times that this website are down and cannot be access for several minutes.

After a thorough research, I decided to convert all of the tables in every database on my server from MyISAM to InnoDB. This is backed up by an article from kavoir.com that explains the pros and cons of the two most common storage engine of MySQL.

Changing the storage engine one by one using phpMyAdmin is a bet of a hassle. So here’s a code to execute the conversion for every database that you have.

1. <?php
   
2. $DB_HOST
   
   =
   
   "localhost"
   
   ;
   
3. $DB_NAME
   
   =
   
   "database"
   
   ;
   
4. $DB_USER
   
   =
   
   "root"
   
   ;
   
5. $DB_PASSWORD
   
   =
   
   ""
   
   ;
   
6. 
   
7. // do not edit the code after this line
   
8. $con
   
   =
   
   mysql_connect
   
   (
   
   $DB_HOST
   
   ,
   
   $DB_USER
   
   ,
   
   $DB_PASSWORD
   
   )
   
   ;
   
9. 
   
10. if
    
    (
    
    !
    
    $con
    
    )
    
11. {
    
12. die
    
    (
    
    'Could not connect: '
    
    .
    
    mysql_error
    
    (
    
    )
    
    )
    
    ;
    
13. }
    
14. 
    
15. mysql_select_db
    
    (
    
    $DB_NAME
    
    ,
    
    $con
    
    )
    
    ;
    
16. $sql
    
    =
    
    "SHOW tables"
    
    ;
    
17. $rs
    
    =
    
    mysql_query
    
    (
    
    $sql
    
    )
    
    ;
    
18. 
    
19. while
    
    (
    
    $row
    
    =
    
    mysql_fetch_array
    
    (
    
    $rs
    
    )
    
    )
    
20. {
    
21. $tbl
    
    =
    
    $row
    
    [
    
    0
    
    ]
    
    ;
    
22. $sql
    
    =
    
    "ALTER TABLE $tbl
    
    ENGINE=INNODB"
    
    ;
    
23. mysql_query
    
    (
    
    $sql
    
    )
    
    ;
    
24. }
    
25. ?>