Optimizing MYSQL?

[FeraTechInc]

Is there a way to optimize MYSQL when making joins so it runs faster with multiple concurrent users?

I've already run the optimization through PHPMYADMIN on all tables.

Any other suggestions would be very much appreciated.

[FeraTechInc]

Forgot to mention,

Running ISPConfig 3 on Ubuntu 9.10
Quad Core Processor with 8gig RAM and 2TB Software Raid 1 Drives

Have already run MYSQL Tuner as well.

[till]

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

[FeraTechInc]

The software we are running is a CMS system called Xoops. Similar to Joomla, Wordpress or Drupal.

The types of database is:
MyISAM

[till]

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/...am-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.

[FeraTechInc]

Is there a simple way of doing this through phpmyadmin?

[till]

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.