Best approach to cache query

Yii 2.0 General Discussions
1

I’m creating a website that list events, each event have multiple tickets, when I’m listening events I need to show the minimum price and the number of tickets.

I have a function on Event model that return the minimum price, I want to cache the result since this query is called multiple times. This are my 2 approaches:

Data Cache with this solution I have about 25 queries per page




/**

     * Return the minimum ticket price for this event

     * @return integer

     */

    public function getMinPrice()

    {

        $cache = Yii::$app->cache;

        $minPrice = $cache->get('eventMinPrice_'.$this->id);

        if($minPrice === false){

            $minPrice = $this->hasMany(Ticket::className(), ['event_id' => 'id'])->where(['ticket.business_type' => Ticket::BUSINESS_TYPE_SELL, 'ticket.status' => Ticket::STATUS_ACTIVE, 'ticket.best_offer' => false])->min('price');

            $cache->set('eventMinPrice_'.$this->id, $minPrice, 120);

        }

        return $minPrice;

    }

Query cache with this solution I have about 55 queries per page:




/**

     * Return the minimum ticket price for this event

     * @return integer

     */

    public function getMinPrice()

    {

        $db = Yii::$app->db;

        $id = $this->id;

        $result = $db->cache(function ($db) use ($id) {

            return $this->hasMany(Ticket::className(), ['event_id' => 'id'])->where(['ticket.business_type' => Ticket::BUSINESS_TYPE_SELL, 'ticket.status' => Ticket::STATUS_ACTIVE, 'ticket.best_offer' => false])->min('price');

        }, 60);

        return $result;

    }

Without cache I have about 123 queries per page.

Why the Query cache do more queries that the data cache? It seams that the best approach is a simple data cache, but I want your opinion.

2

I think this part could be simplified like the following:




    $minPrice = Ticket::find()->where([

        'event_id' => $this->id,

        'business_type' => Ticket::BUSINESS_TYPE_SELL,

        'status' => Ticket::STATUS_ACTIVE,

        'best_offer' => false,

    ])->min('price');

I mean that you don’t need to use ‘hasMany’ relation here. You could retrieve the minimum price from the ticket table in a simpler way.

And, this is my personal opinion, but the granularity of caching in your code looks too small.

I’m not sure. Try measure the consumption time (not the count of sqls) with and without cache.

There’s a trade off in the chunk size and/or complexity of data to be cached.

When it’s small and simple, the hit rate of cache will be high. But the performance boost will be small because the overhead of caching procedure will be relatively high. It may even damage the performance sometimes.

When the chunk size is large and/or the process to create it is complex, the things are opposite way round. The hit rate will be low but the performance boost will be great once it is hit.