PHP theres some Impact using long text in a associative array?

Question

Im doing a database class in PHP and I want to make cache of the result of the querys in a associative array, My idea is to use the sql statment as the index of the cache array, its could be a good idea? or should I use a md5 from the sql?

class DB{
const HOST = 'localhost'; //Your Database Host!
const USER = 'user'; //Your Database Username!
const PASSWORD = 'pass'; //Your Database Password!
const DATABASE = 'database'; //Your Database Name!

private static $Instance;
private static $cache = array();

private function __construct(){
 self::$Instance = mysql_connect(self::HOST, self::USER, self::PASSWORD) or die("Could not connect to database server<br/><b>Error:</b>".mysql_error());
 mysql_select_db(self::DATABASE) or die("Could not connect to database<br/><b>Error:</b>".mysql_error());
 return self::$Instance;
}

public static function DB(){
 if(!isset(self::$Instance)){
   $c = __CLASS__; 
   new $c(); 
 }
 return self::$Instance;
}

public static function QueryUnique($query){
 $query = "$query LIMIT 1";
 //$h = md5($query);
 $h = $query;
 if(isset(self::$cache[$h]))return self::$cache[$h];

 $result = mysql_query($query, self::DB());
 self::$cache[$h] = mysql_fetch_array($result);
 return self::$cache[$h];
}

}

Good Day

Answer 1

A hash (md5, sha1) will eat less memory space.

Answer 2

caching mysql data could be a little risky, it makes a lot of assumptions about things, however having said that a md5 checksum of the sql string is an good way of generating a id for your cache data, however you'll need to normalise the sql first, for example:

'select 1+2'
'select 1 + 2'
' select 1 +2'

each one will give a different checksum, you'll need to make sure any little differences between the same query are taken care of.

Answer 3

If the query is quite long, I'd go for using some kind of hash (like md5) :

• it will probably use a bit less memory (not that relevant, I suppose)
• it will allow you to store that in some other kind of cache, like file or APC or memcached or whatever without having to change the index.

Considering the low risks of collisions with md5, it doesn't seem "dangerous" to do that.

The only problem I see is it might get harder for you to find your cached data when debugging, be it with var_dump or any kind of "real" debugger :-(

Answer 4

There shouldn't be a large impact, apart from a little extra memory usage, but compared to the result of the query, I don't think it will be a problem.

Associative arrays are ordered maps, so finding the right index shouldn't be affected too much by the total length of the string. The only downside would be that most strings start with the exact same text.

I wouldn't rely on just an MD5 hash for your queries. It's possible (if unlikely) that a hash collision would occur, and it would select a completely different query result. It might be an acceptable risk for your application however.

Personally, I wouldn't do this at the query level at all. The cacheability usually depends on the type of data returned. If you cache at this level, your application would be completely agnostic of what it is caching.

Answer 5

Before you get too far through your implementation, you should know that mysql does its own query caching and it has several major advantages over your implementation:

• The cache is shared among all PHP requests.
• Cached results are automatically cleaned up when the table data changes.
• The cache is limited to a certain memory size (rarely-used queries will be dropped out of the cache)