Php and mysql caching

Question

I am currently working on a php/mysql project with the AbleDating system, my customer is worried about server load so he asked me to use "caching" as much as I could, he asked me to cache mysql query and some html zones...

Is that possible to cache only some html zones with php? If yes how can I do this?

For the mysql caching is it just an option to check or must I change something in the coding?

Thanks!

Answer 1

MySql caching basically just caches resultsets against SQL issued to the database: if the SQL statement/query is in the cache, then the resultset gets returned without any work being done by the database engine. There is thus a certain amount of overhead in maintaining accuracy (i.e. the DB must track changes and flush cache entries accordingly).

Compare this to other DBs such as Oracle, where the caching mechanism can take into account placeholders (bound variables) and omits a "hard" parse (i.e. checking if the SQL is valid etc.) if the SQL plan is found in the SQL common cache.

If you find yourself repeatedly submitting identical SQL to the database, then caching may make a substantial difference. If this is not case, you may even find that the additional overhead cancels out any benefit. But you won't know for sure until you have some metrics from your system (i.e. profiling your SQL, analysing the query logs etc.)

Answer 2

Sure caching is very important. I use self a php chacher called minicache have a look http://code.google.com/p/minicache/

Answer 3

memcached is a great way to cache anything (PHP, mysql results, whatever) in memory. Couple it with an easy to use caching library like Zend_Cache and it makes caching a cinch:

$frontendOptions = array(
   'lifetime' => 60, // Seconds to cache
   'automatic_serialization' => true
);

$cache = Zend_Cache::factory('Core',
                             'Memcached',
                             $frontendOptions);

if (!$my_nasty_large_result = $cache->load('my_nasty_large_result')) {
    $nasty_big_long_query = $db->query('SELECT * FROM huge_table');
    $nasty_big_long_result = array();
    foreach ($nasty_big_long_result as $result)
     $nasty_big_long_result[] = $result;

    $cache->save($nasty_big_long_result, 'my_nasty_large_resultt');
}