PHP backup log table from MySQL I have been working on a little project to tidy up my log table which has exceeded the shared host provider limits:) So I have done some script that is working except one thing at the end: optimise DB.

This script will run in cron so I hope security should not be issue.

My questions are:


the code is working, however I would like to learn how to do it better, so If you could point out thing that I shouldn't do and I did I would appreciate
if someone could tell me why my optimise DB doesn't work I would also thanks.
Performance!!! It will run on a table which has 1million rows and I am worried that his script wont perform fast enough. Any idea how to work this out?


I know I should use PDO (next step - as I am not familiar with it yet) but as a first step I am proud that I could put together something that is working. 

&lt;?php
include 'opendb.php';

//functions
function test($string){
  echo "&lt;p&gt;".$string."&lt;/p&gt;";
}

function db_rows($db,$ord){
  $dbquery="SELECT azon FROM $db ORDER BY azon $ord LIMIT 1";
  $dbresult=mysql_query($dbquery);
  $row = mysql_fetch_array($dbresult);
  $dbrow = $row['azon'];
  return $dbrow;
}
// end of functions

//config information...
$acttable  = 'foo';
$today = date("yW_Hi");
$newdb = 'test_'.$today;

$firstact= db_rows($acttable,"asc");
$lastact= db_rows($acttable,"desc");
$upto=$firstact+25000;

test($lastact);
test($firstact);


if ($lastact-$firstact&gt;50000) {

//create a new table 
$newdbsql=" CREATE TABLE  $newdb ( `azon` bigint(20) NOT NULL AUTO_INCREMENT,  `mikor` datetime NOT NULL,  `felhazon` int(11) NOT NULL,  `felhnev` varchar(255) NOT NULL,  `muvelet` varchar(20) NOT NULL,  `sql` varchar(255) NOT NULL,  `tabla` varchar(255) NOT NULL,  `mezok` varchar(255) NOT NULL,  `ertekek` text NOT NULL,  `feltetel` varchar(255) NOT NULL,  `ip` varchar(255) NOT NULL,  `bongeszo` varchar(255) NOT NULL,  PRIMARY KEY (`azon`),  KEY `mikor` (`mikor`),  KEY `felhazon` (`felhazon`),  KEY `felhnev` (`felhnev`),   KEY `muvelet` (`muvelet`),  KEY `tabla` (`tabla`),  KEY `feltetel` (`feltetel`)) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3524772 ;" ;
test($newresult);
$newresult = mysql_query($newdbsql);


// copy all the data
$query = "INSERT INTO $newdb SELECT * FROM $acttable WHERE $acttable.azon &lt; $upto";
test($query);
$result = mysql_query($query);

// so what has happened...
if ($result) {
   test("ok");
  $delquery = "DELETE FROM $acttable WHERE $acttable.azon &lt; $upto";
  test($delquery);
  $delresult = mysql_query($delquery);
  if ($delresult) {
     test("Deleted rows - OK");
  } else {
     test("failed to delete...");
     // then tidy up everything:)
    $res = mysql_query('SHOW TABLE STATUS WHERE Data_free / Data_length &gt; 0.1 AND Data_free &gt;  102400');
    while($optrow = mysql_fetch_assoc($res)) {
             mysql_query('OPTIMIZE TABLE ' . $optrow['Name']);
         }
      }
   }
   else {
      test("failed to copy...");
   }

}
else {
  test("no work needs to be done.");
}


// close db
mysql_close($conn);
?&gt;
Your Select statement.

SELECT azon FROM $db ORDER BY azon ASC/DESC LIMIT 1


you could simplify the statement like this:

SELECT MAX(azon)/MIN(azon) AS 'azon' FROM $db


There are some discussions about the gain of performance by using MIN/MAX instead of ORDER BY + LIMIT. I personally experienced that MIN/MAX is faster, especially on tables with many rows.
You can simply copy a table structure by using

CREATE TABLE `$newdb` LIKE `$acttable`


but you'd have to set the AUTO_INCREMENT manually after this, otherwise it would be set to 1.

ALTER TABLE `$newdb` AUTO_INCREMENT=3524772


(I don't see the purpose on this, maybe it's not even nessecary?)
Your optimize.


I'm quite irritated about the way to want to query this. You're calling that statement inside the else-condition -> after your delete has failed. It should be called inside the if-condition (or I missunderstood the goal).
You should consider using InnoDB instead of MyISAM (in general), because there would be no need (and possibility) of running an OPTIMIZE.

There should be no performance issue on the php-side as long as you do not iterate through the many rows.
You may want to increase the max_execution_time-directive to prevent the script from stopping, but I consider this is not nessecary.

On MySQL-side, i really suggest changing to InnoDB (you may want to read this).




Conclusion:
Besides the little changes of the statements and the use of OPTIMIZE inside your if-condition, there are no required improvements for exactly this script in my opinion. I strongly recommend to change the database engine when you make your changes for using PDO. This of course means more effort, as there are many variables which you can or should set to the InnoDB engine, but I think it is worth it. In fact, there aren't real advantages on using MyISAM, less then ever in performance.