Amyst Design Company, web and print design and development services

Recently on a project it became necessary to copy data from one database table to another, identical table. The theory is that once data is data, it is data forever. Meaning this; Just because it isn’t useful information at the moment doesn’t necessarily mean that it will always be useless. Long story short, if you have data… Keep it.

Of course it is possible to just SELECT the data from table_1, INSERT it into table_2 then DELETE the original record from table_1, but why do that when MySQL has built in a shortcut? Enter, the INSERT/SELECT hybrid query.

INSERT INTO table_2
SELECT * FROM table_1 t1
WHERE ( t1.id_field = 'some_unique_id' );

After that, just follow it up with a standard DELETE query to remove the original record.

DELETE FROM table_1
WHERE ( id = 'some_unique_id' );

Yesterday, charged with the task of figuring out why a web page [NOT programmed by Amyst Design] was taking upwards of 45.00 sec. to load we successfully reduced load time to a respectable 0.02 sec.

The first order of business was to implement a page load timer, to get real – empirical – data to gauge our improvement.

At the top:

// timestamp function, use this to measure page load time
$time_start = explode(' ', microtime());
$time_start =  $time_start[1] + $time_start[0];

At the bottom:

$time_finish = explode(' ', microtime());
$time_elapsed = $time_finish[0] +  $time_finish[1] - $time_start;
printf('<p style="text-align:center;">Page loaded in %.3f seconds.</p>',  $time_elapsed);

Once this code was in place, loaded the page (25) times on (2) CPUS on the same network and came up with average load time of 46.72 sec.

The page wasn’t particularly complicated, nor did it contain more than the average number of database queries or loops ( for, while, foreach ). After going through all 700+ lines of code from top to bottom and fixing process hogs on nearly every line, thinks like double quote encapsed strings with no variables in them to parse or no real benefit to parsing a variable vs. concatenating a string around a variable. Also mentionable were a great many associative array fixes, more a pet peeve than a real process killer.
From:

$array[key]="string of text with no variable to parse.";

To:

$array['key'] = 'string of text with no variable to parse.';

After all these changes there was no real marked improvement, with an average load time of 42.21 sec it wasn’t a real improvement. That was when a closer look at the loops were taken, and the culprit was found.

$result = mysql_query("SELECT * FROM widgets WHERE ( widget_status = '{$user_selected_status}' );");
while ( $widget = mysql_fetch_object($result) )
{
    // process several data preparation routines than...
    // jump out of the php <?php ?> brackets and print a very, very long XHTML table
}

Looking at this it became immediately apparent that within the while-loop there were at least (11) variables/arrays/objects being defined and re-defined every time the loop iterated, but no where had the programmer unset a single one, leaving the PHP parser to chug like a workhorse through a peat bog in Ireland in June, descriptive yes, and just as deadly.

after implementing:

$result = mysql_query("SELECT * FROM widgets WHERE ( widget_status = '{$user_selected_status}' );");
while ( $widget = mysql_fetch_object($result) )
{
    // process several data preparation routines than...
    // jump out of the php <?php ?> brackets and print a very, very long XHTML table

    // lets cut the parser some slack...
    unset($objects, $arrays, $variables);
}

After the ‘unset()’ fix the average load time of this page dropped to a more expected 0.02 sec.

What do we gather from this? Don’t waste your time and money on hobbyist programmers, your project, your business and ultimately your bottom line will suffer for it.