Bastiaans Blog

Recently I had to improve performance of several PHP sites. One of the problems is complicated queries, that take a long time to compute by the MySQL server. Therefore, I decided to look for a solution.

I found a nice solution on http://pureform.wordpress.com/2008/05/21/using-memcache-with-mysql-and-php/ and I decided to work this out for my sites.

For php4 sites I rewrote this code to a simple class:

/**
* Bastiaan Welmers - 20081013
* instataniate this class and use mysql_query_cache instead of mysql_query for slow queries
*
* php4 style class
*
*/

class memCacheDb
{
	var $memcache;

	// constructor
	function memCacheDb()
	{
		# Connect to memcache:
		$this->memcache = new Memcache;
		$this->memcache->connect('localhost', 11211) or die ("Could not connect to memcache");
	}

	# Gets key / value pair into memcache ... called by mysql_query_cache()
	function getCache($key) {
		return ($this->memcache) ? $this->memcache->get($key) : false;
	}

	# Puts key / value pair into memcache ... called by mysql_query_cache()
	function setCache($key,$object,$timeout = 60) {
		return ($this->memcache) ? $this->memcache->set($key,$object,MEMCACHE_COMPRESSED,$timeout) : false;
	}

	# Caching version of mysql_query()
	function mysql_query_cache($sql, $linkIdentifier = false, $timeout = 600 /* = 10 minutes expiration */) {
		if (!($cache = $this->getCache(md5("mysql_query" . $sql)))) {
			$cache = false;
			$r = ($linkIdentifier !== false) ? mysql_query($sql,$linkIdentifier) : mysql_query($sql);
			if (is_resource($r) && (($rows = mysql_num_rows($r)) != 0)) {
				for ($i=0;$i<$rows;$i++) {
					$fields = mysql_num_fields($r);
					$row = mysql_fetch_array($r);
					for ($j=0;$j<$fields;$j++) {
						if ($i == 0) {
							$columns[$j] = mysql_field_name($r,$j);
						}
						$cache[$i][$columns[$j]] = $row[$j];
					}
				}
				if (!$this->setCache(md5("mysql_query" . $sql),$cache,$timeout)) {
					die('Error trying to connect to memcache');
					# If we get here, there isn't a memcache daemon running or responding
				}
			}
		}
		return $cache;
	}
}

For the PHP5 applications I wrote a PHP5 style style class and I added expiration logic. You can now use MemCacheDb::deleteMatching($arrayOfKeywords) to remove queries from cache that contain certain keywords. You can use this for example in your CMS, just call the deleteMatching method in the update/creation logic with the updated table name in the keywords and all cached queries will expire.

/**
* Bastiaan Welmers - 20081013
* instataniate this class and use this mysql_query methoid instead of mysql_query for slow queries
* it wil return a array with assosiative arrays with the output.
* So use foreach($mdb->mysql_query($query) as $rows) {}
*
* php5 style class
*
*/

class MemCacheDb
{

	private $_memcache;
	private $_appKey = 'Change this key for your application';
	private $_queries = null;

	// constructor
	public function __construct()
	{
		// Connect to memcache:
		$this->_memcache = new Memcache();
		$this->_memcache->connect('localhost', 11211) or die ("Could not connect to memcache");
		$this->_appKey = md5(__FILE__ . $this->_appKey);
	}

	public function __destruct()
	{
		if (is_array($this->_queries))
		{
			$this->_saveQueries();
		}
	}

	public function getStats()
	{
		return $this->_memcache->getStats();
	}

	private function _loadQueries()
	{
		if (!is_array($this->_queries))
		{
			$queries = $this->_memcache->get($this->_appKey . '__queries');
			if ($queries !== false && is_array($queries))
				$this->_queries = $queries;
			else
				$this->_queries = array();
		}
	}

	public function getQueries()
	{
		$this->_loadQueries();
		return $this->_queries;
	}

	public function searchQueries($search)
	{
		$queries = array();
		foreach($this->getQueries() as $sql => $timestamp)
		{
			if (strpos($sql, $search) !== false)
			{
				$queries[] = $sql;
			}
		}
		return $queries;
	}

	public function deleteQuery($sql)
	{
		$this->_memcache->delete($this->_getQueryKey($sql));
		$this->_loadQueries();
		if (isset($this->_queries[$sql]))
			unset($this->_queries[$sql]);
		$this->_saveQueries();
	}

	public function deleteMatching($searchItems)
	{
		if (!is_array($searchItems))
		{
			$searchItems = array($searchItems);
		}

		foreach($searchItems as $searchItem)
		{
			foreach($this->searchQueries($searchItem) as $query)
				$this->deleteQuery($query);
		}
	}

	public function deleteAll()
	{
		foreach($this->getQueries() as $sql => $timestamp)
		{
			$this->deleteQuery($sql);
		}
	}

	private function _saveQueries($timeout = 1800)
	{
		if (is_array($this->_queries))
		{
			// clean old stuff
			foreach($this->_queries as $sql => $timestamp)
			{
				if (time() - $timestamp > $timeout)
					unset($this->_queries[$sql]);
			}

			return $this->_memcache->set($this->_appKey . '__queries', $this->_queries, 0, $timeout);
		}
		else
			return false;
	}

	// Gets key / value pair into memcache … called by mysql_query_cache()
	private function _getCache($key) {
		return ($this->_memcache) ? $this->_memcache->get($key) : false;
	}

	// Puts key / value pair into memcache … called by mysql_query_cache()
	private function _setCache($key, $object, $timeout) {
		$this->_loadQueries();

		if ($this->_memcache instanceof Memcache)
			return $this->_memcache->set($key, $object, 0, $timeout);
		else
			return false;
	}

	private function _cacheQuery($sql, $object, $timeout)
	{
		if ($this->_setCache($this->_getQueryKey($sql), $object, $timeout))
		{
			$this->_loadQueries();
			$this->_queries[$sql] = time();
			$this->_saveQueries($timeout);
			return true;
		}
		else
		{
			return false;
		}
	}

	public function expireQuery($query)
	{
		$this->_memcache->delete($this->_getQueryKey($query));
	}

	public function mysql_query($sql, $linkIdentifier = false, $timeout = 600)
	{
		return $this->mysql_query_cache($sql, $linkIdentifier, $timeout);
	}

	private function _getQueryKey($sql)
	{
		return md5($this->_appKey . $sql);
	}

	// Caching version of mysql_query()
	public function mysql_query_cache($sql, $linkIdentifier = false, $timeout = 600 /* = 10 minutes expiration */) {
		if (!($cache = $this->_getCache($this->_getQueryKey($sql)))) {
			$cache = array();
			$r = ($linkIdentifier !== false) ? mysql_query($sql,$linkIdentifier) : mysql_query($sql);
			if (is_resource($r) && (($rows = mysql_num_rows($r)) != 0)) {
				for ($i=0;$i<$rows;$i++) {
					$fields = mysql_num_fields($r);
					$row = mysql_fetch_array($r);
					for ($j=0;$j<$fields;$j++) {
						if ($i == 0) {
							$columns[$j] = mysql_field_name($r,$j);
						}
						$cache[$i][$columns[$j]] = $row[$j];
					}
				}
				if (!$this->_cacheQuery($sql, $cache, $timeout)) {
					die('Error trying to connect to memcache');
					// If we get here, there isn't a memcache daemon running or responding
				}
			}
		}
		return $cache;
	}
}

TODO: this code need to get more documentation, and a static method to store the object instance so it can be called everywhere in the application.