How to speed up your Oempro Database

Oempro uses MyISAM as the Database structure. We all know that Oempro is not the fastest when it comes to Database queries.

In order to speed up the queries we have to convert MyISAM tables to InnoDB. This will give you a huge speed improvement and take a lot of load of your CPU.

Depending on your server resources and tables, this can take a long time to do, especially if you have subscriber contacts tables exceeding 1 million rows or more. Some systems may even become unstable while the converting happens. We haven’t felt such a high impact as we run all our Oempro Databases on SSD drives in Raid.

To get started, copy the code below and upload it to you server.
IMPORTANT: Make a full backup of your DB first. I don’t take any responsibility if you loose any of your data. Use this script at your own risk.

Enter your DB connection details.  Around line 32, we chose to run 20 tables at a time. Chose to your liking. On slow servers I recommend 5-10 tables at a time, with more powerful servers, you can use 20-unlimited. It should be easy to spot on how to change it in the code.

Once you have converted all the tables you can add a cron job to run once a day. This is necessary to convert the new created tables, as they still get created in MyISAM.

Also have a look at you innodb_buffer_pool_size. Make sure you have a high enough value in there.

<?php

date_default_timezone_set("UTC");
$db_name = 'YOURDBNAME';

$db_user = 'YOUDBUN';

$db_password = 'YOURDBPW';

$db_host = 'YOURDBHOST';

$main_link = mysql_connect($db_host, $db_user, $db_password);
if (!$main_link) {
    die('Not connected : ' . mysql_error());
}

$db_selected1 = mysql_select_db($db_name, $main_link);
if (!$db_selected1) {
    die ('Can\'t use '.$db_name.' : ' . mysql_error());
}

echo "Start Time: " . date("Y-m-d H:i:s"). "\n";
$CountSQL = "SELECT count(*) FROM information_schema.TABLES WHERE table_schema = 'YOURDBNAME' AND Engine='MyISAM';";
$ResultCount = mysql_query($CountSQL);

$CountRow = mysql_fetch_array($ResultCount);
$CountOfTables = $CountRow[0];
echo "Total Number of MyISAM Tables: $CountOfTables\n\n";
$sql = "SELECT TABLE_NAME, table_rows FROM information_schema.TABLES WHERE table_schema = 'YOURDBNAME' AND Engine='MyISAM'  order by table_rows ASC LIMIT 20;";
$rs = mysql_query($sql);
$MyISAMTables = array();
$i = 1; 
while($row = mysql_fetch_array($rs))
{
    $tbl = $row[0];
    $CheckEngineQuery = "show table status where Name='$tbl'";
    $EngineResult = mysql_query($CheckEngineQuery);
    $EngineRow = mysql_fetch_assoc($EngineResult);
    if($EngineRow["Engine"] == "MyISAM")
    {
        $MyISAMTables[] = $tbl; 
        $i++;
    }

    if($i > 10)
    {
        break;
    }
}

echo "Mysql MyISAM Tables in DB: \n" . implode("\n", $MyISAMTables) . "\n";
foreach($MyISAMTables as $TableName)
{
    echo "Processing $TableName\n";
    $sql = "ALTER TABLE $TableName ENGINE='InnoDB'";
    mysql_query($sql);
    echo mysql_error();
    echo "Converted $TableName to INNODB\n";
}

echo "End Time: " . date("Y-m-d H:i:s") . "\n";