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.

``
".$string."";
}

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>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 DEF

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

StackExchange Code Review Q#14296, answer score: 4