Magento 1: caching SQL queries using Magento cache

Question

Theorically, you can cache anything using Mage::app()->saveCache() and I recently came accross some code that could potentially cache the product load:

<?php

class Vendor_Module_Model_Product extends Mage_Catalog_Model_Product
{
    public function load($id, $field = null)
    {
        if (null !== $field || !Mage::app()->useCache('product_loading')) {
            return parent::load($id, $field);
        }

        // Caching product data
        $storeId = (int) $this->getStoreId();
        $cacheId = "product-$id-$storeId";
        if ($cacheContent = Mage::app()->loadCache($cacheId)) {
            $data = unserialize($cacheContent);
            if (!empty($data)) {
                foreach ($data as $key => &$value){
                    $this->$key = $value;
                }
                unset($value);
            }
        } else {
            parent::load($id);

            // You can call some heavy methods here

            try {
                $cacheContent = serialize(get_object_vars($this));
                $tags = array(
                    Mage_Catalog_Model_Product::CACHE_TAG,
                    Mage_Catalog_Model_Product::CACHE_TAG . '_' . $id
                );
                $lifetime = Mage::getStoreConfig('core/cache/lifetime');
                Mage::app()->saveCache($cacheContent, $cacheId, $tags, $lifetime);
            } catch (Exception $e) {
                // Exception = no caching
                Mage::logException($e);
            }
        }

        return $this;
    }
}

That code is a draft but theorically, that could work fine is you take the store id into consideration.

So I'm wondering :

• if there is any limits / cons of caching SQL query results ?
• what should I keep in mind / take into consideration if I want to implement such feature ?

Things I already got in mind:

• beware of the differences in product data between two different stores
• refresh the cache on product save
• take care of the inventory loaded with each product load. (maybe use __sleep / __wakeup to only load the inventory on product load)

NB: I'm aware that we usually let MySQL Query Cache take care of that, I'm just wondering how far can the Magento cache go.

Answer 1

It is a bad idea to cache MySQL queries when you can optimize them. You might think that caching will solve performance issues. But in the end, it adds another layer of I/O calls in the system, that will shoot you back later with higher server expenses as cache size will grow dramatically.

I advise you to take a look with profiler what is the slowest part of the process and optimize it, do not try to hide problem behind caching, it won't help in the long run, especially if you need to prewarm it. In my experience, there are no areas which cannot be optimized, and Magento is not using MySQL at its edge at all. Most of the optimization just ending up with forcing right indexes, reducing the number of joins, changing query execution strategy, etc.

I know it is not an answer to your particular problem, but I don't want you to experience same issues as other devs experienced before. Caching is for scaling, but not for speeding up the process where other options for optimization are available.