Posted by Mike P2 on 04/28/07 18:38

I did some more tests (I am working on a multi-lingual site also so
this is useful to me too), and it looks like reading and unserializing
a serialized language array from a file is much quicker than reading
and unserializing from a database (on my server environment). The
first two times are using PHP to create a PHP file and then
include()ing it

Array
(
[create file] => 0.0070760250091553
[direct read] => 0.0096750259399414
[write serial] => 0.0053510665893555
[read serial] => 0.00060105323791504
[insert db serialized] => 0.054249048233032
[get db serialized] => 0.037890911102295
)

Here's the (PHP5) test that you can try on your server:
<?php
$db = new mysqli( 'dbhost', 'dbuser', 'dbpass' );
$db->query( 'USE `dbtable`' );

$i = 0;
$lang = array();

define( 'DATAFILE', 'benchmarking.php' );

//create file
$data = '';
$times = array( 'create file' => microtime( true ) );
$data = "<?php\n\$lang=array('asdfasdfasdf'";
for( $i = 20000; $i < 20500; ++$i )
$data .= ",'asdfewqrhbeqstn{$i}asdfasdfasd{$i}fasdf'";
$data .= ');?>';
file_put_contents( DATAFILE, $data );
$times['create file'] = microtime( true ) - $times['create file'];

unset( $data );

//direct read
$times['direct read'] = microtime( true );
require( 'benchmarking.php' );
$times['direct read'] = microtime( true ) - $times['direct read'];

//write serialized
$times['write serial'] = microtime( true );
file_put_contents( DATAFILE, serialize( $lang ) );
$times['write serial'] = microtime( true ) - $times['write serial'];

$lang = array();

//read serialized
$times['read serial'] = microtime( true );
$lang = unserialize( file_get_contents( DATAFILE ) );
$times['read serial'] = microtime( true ) - $times['read serial'];

unlink( DATAFILE );

$data = '';

//db serialized
$times['insert db serialized'] = microtime( true );
$data = serialize( $lang );
$sql_query = 'INSERT INTO `bm2` ( `data` ) VALUES ('
."'$data' )";
$db->query( $sql_query )
or trigger_error( $db->error, E_USER_ERROR );
$times['insert db serialized'] = microtime( true ) - $times['insert db
serialized'];

$data = '';
$lang = array();

//get db serialized
$times['get db serialized'] = microtime( true );
if( !( $sql = $db->query( "SELECT `data` FROM `bm2` WHERE `id` = $db-
>insert_id" ) ) )
trigger_error( $db->error, E_USER_ERROR );
list( $data ) = $sql->fetch_row();
$lang = unserialize( $data );
$times['get db serialized'] = microtime( true ) - $times['get db
serialized'];

print_r( $times );
?>

The table looks like:
`bm2` (collation is utf-8)
`id` PRIMARY INT NOT NULL auto_increment
`data` TEXT

[Back to original message]