Optimizing MYSQL?

Which software causes the load on mysql? Which mysql table type do you use in that database?

 

If the cms uses a lot of joins, then it might be faster to use innodb instead of myisam.

See also:

http://www.mysqlperformanceblog.com/2006/05/29/join-performance-of-myisam-and-innodb/


What you can try is this:

1) Make a backup of the database, in case that you have to switch back or something goes wrong when changing table type.
2) Change the database table type to innodb for all myisam tables.

as alternative, you can e.g. make a copy of the database in mysql, change type to innodb and then change the database name in the cms to the new database.

 

If you like to follow my second alternative (doing a copy of the db), then you can do everything in phpmyadmin:

1) select the database, go to "operations" > "Copy database to", enter a new database name, select "Structure and data", "CREATE DATABASE before copying", "Add AUTO_INCREMENT value" and "Switch to copied database" and click on go. This should create a copy of the database with all data and switch phpmyadmin directly to the new database.
2) Now, in this new database, click on every table that is of type myisam, click on "operations", go to "Storage Engine" and select "InnoDB" instead of "MyIsam" and click on "go". Repeat this for every table in the database.

An alternative method might be to:

1) Export the database in phpmyadmin and save it as .sql file.
2) Edit the .sql file with a text editor and replace all occurrences of "ENGINE=MyISAM" with "ENGINE=InnoDB".
3) Create a new database in phpmyadmin and import the sql dmp again.

But depending on the size of your database, the db might be too large for phpmyadmin to export and import it, in that case you would have to use the mysqldump command on the shell for the export and the mysql command for reimporting the db.