QUERY CACHE - Client Side Query Cache

function mysql_cache_enable($db,$on_off){

/*

    this function change a flag in mysql protocol, after each query executed it return the table checksums that allow client-side query cache

*/

}

function mysql_cache_cacheable($result) 

/*

    return true/false if query can be cacheable (drop,set,information schema, etc can't) 

    this function will change the query protocol, adding cacheable flags information 

*/

function mysql_cache_used_tables($result)

/* 

  this function will change the query protocol, adding table+checksum information when cacheable flag is true

  return a array of all tables(partition) used in query and a counter/checksum 

  counter/checksum are changed when update/delete/alter (cache invalidation)

  it's stored in .frm file when table is closed, this reduce i/o on disks

  it's nice put information about server startup too

  array('db.table1'=>'2012-03-03 00:00:00;12345;adsfadsfkljasdfnweoinadsf',

	'db.table2'=>'2012-03-03 00:00:00;12345;adsfadsfkljasdfnweoinadsf'));

  )

*/

function mysql_cache_sql_flags($result)

/*

  maybe this could be done in mysql protocol, or using the mysql client library? maybe server side is prefered, in this case this function will change the query protocol, adding sql_flags information when cacheable flag is true

  return flags that change query cache results, example

  using a different sql_mode, change the results of a query

  it should be a binary string "!MFA)SDF_#RMÇa`p|_f)qi!@#$"

  the schema used is a 'flag' too, see more information about flags in new qc_info.cc plugin and sql_cache.h/sql_cache.cc

*/

function mysql_cache_get_tables($db,$array)

/*

  this function add a new function to mysql protocol, it's not query() it's a new function like mysql_select_db()

  return an array with the checksum of each object, like mysql_cache_used_tables(), but now consulting mysql server

  it's like mysql_query, but instead of executing a query, it return up to date table checksum information, that help us to invalidade client side query cache

*/

function mysql_cache_check_tables($cache_tables,$mysql_tables)

/*

  execute the check of a $cache_tables array, and mysql_cache_get_tables array return

  i'm telling it's a array but maybe could be a internal resource, just to explain the api

*/

function mysql_cache_hash_sql($db,$SQL)

/*

  return an hash to use in cache systems (memcache / shared memory or others), example:

  hash = mysql_cache_sql_flags + $SQL

*/

function qc_invalidate_table($array){

  check if some table have different hash using $array (mysql_cache_used_tables($result))

  remove all queries that use this tables (like mysql query cache do removing queries from cache)

}

function qc_invalidate_hash($hash){

  remove one query from cache via $hash (mysql_cache_hash_sql)

}

function qc_put_table($result){

  put the tables checksum in tables checksum cache, using mysql_cache_used_tables

  it's done in qc_put()

}

function qc_put($db,$result){

  qc_invalidate_table(mysql_cache_used_tables($result)); /* invalidate queries that have old table checksum */

  qc_put_table($result);

}

function qc_get_tables($hash){

  return the tables / checksum used in a query cached of a given $hash (using the - mysql_cache_hash_sql)

}

function qc_exists($hash){

  return if a hash exists in the query cache (return true/false)

}

function qc_result/qc_fetch_assoc/qc_free/others($parameters){

/*

  return via cached query or via mysql result 

  this could replace mysql functions in all cases

*/

  if($resource == qc_resource)

    execute the qc_function that return data

  else

    execute the mysql_function that return data

}

function qc_garbage_colector(){

   execute garbage colector, when using background invalidation

}

function qc_query($db,$SQL) {

  /* execute the query with query cache instead of mysql_query */

  $SQL_HASH=mysql_cache_hash_sql($db,$SQL);

  if (qc_exists($SQL_HASH)) {

	$qc_tables	=qc_get_tables($SQL_HASH);

	$mysql_tables	=mysql_cache_get_tables($db,$qc_tables);

	if (!mysql_cache_check_tables($qc_tables,$mysql_tables)) {

		// old query, must clean our cache (or maybe mark it and garbage colector in in background?)

		qc_invalidate($qc_tables);

	} else {

		// nice! a query cached!!!

		return qc_resource object that contains rows, and can be used in qc_result,qc_fetch_assoc and others qc_functions

	}

  }

  // oh no... our query was not cached... let's execute it and check if we can cache

  $result=mysql_query($db,$SQL);

  // maybe we could invalidate old queries via tables information... check that we do it to UPDATE/DELETE/ALTER and others queries different from SELECT

  qc_invalidate_table(mysql_cache_used_tables($result));

  if(mysql_cache_cacheable($result)){

        // nice a cacheable query

        // let's cache it!

	qc_put($db,$result);

  }

  return $result; // return as a mysql_query result

}

a result block

a query block

a table block

the query block links the table block and the result block

when we check a table change, we read table block, and if we need to remove a query we go to queries that use this table block and remove the result block and the query block

when we need result about a query we go to query block via hash and find the result block

check that we can use a result block for two query blocks... this i nice since we can cache the send sql string, and a 'optimized' version of that query, this allow a very high hit rate query cache, but increase memory use (that's not a problem since the memory will be in many servers and not in mysql server alone machine)

$db=mysql_connect(); // maybe add a flag to enable/disable client-side query cache?

mysql_cache_enable(true);

$SQL="some query";

$result=qc_query($SQL);

for ($i=0;$i<qc_num_rows($result);$i++)

	print_r(qc_fetch_assoc($result));

qc_free($db);

mysql_close($db);

qc_garbage_colector(); // clean our garbage