Redis + MySQL

mysqlredis_logosymfony_black
Redis is a key-value database. No SQL. It’s not only a cache, it’s much more than that. You can opperate over keys and expire them. You can use Redis even as a queue system. But what is the use we are going to give today to redis?

Using Redis to cache MySQL queries and MySQL result sets

If you happen to have big queries, slow queries or even simple queries you will enjoy this guide. The time to generate a query to send it to MySQL, to fetch the resultset and to turn it into an object is much much faster if you just ask for the resultset to redis. But how can we manage this? How can we make it easy to store all the info, the metadata, the queries, the results in redis?

Symfony 2 + Doctrine + SNC Redis

First say that this works with any framework, I have examples of redis in phalcon, but here we are using symfony as it’s much more used framework.

As any symfony developer knows, we install via composer the snc_redis component.

{
    "name": "symfony",
    ...
    "require": {
        ...
        "snc/redis-bundle": "1.1.*"
    }
}

Update your libraries with composer:

composer update

Configuring SNC Redis

We will edit our symfony configuration file “app/config/config.yml” and we will add the configuration. We are telling to the module that the metadata, the queries and the results will be stored in redis.

It’s important that you have installed the php-redis extension. Be sure that you have that checkpoint achieved.

snc_redis:
    clients:
        default:
            type: phpredis
            alias: default
            dsn: %redis_dsn%
        doctrineCache:
            type: phpredis
            alias: doctrineCache
            dsn: %redis_dsn%
    doctrine:
        metadata_cache:
            client: doctrineCache
            entity_manager: your_name
            document_manager: default
            namespace: "DataCache::"
        result_cache:
            client: doctrineCache
            entity_manager: your_name
            namespace: "ResultCache::"
        query_cache:
            client: doctrineCache
            entity_manager: your_name
            namespace: "QueryCache::"

Doctrine And Redis

Now Redis should have a lot of keys holding the SQL executed by Doctine and all the metadata from the tables that doctrine supports. This has probably fasten your application but we haven’t started yet with the fun. Lets cache some result sets in Redis.
Lets begin with an example.

            $this
            ->createQueryBuilder('u')
            ->where('u.username= :username')
            ->getQuery()
            ->useResultCache(true, self::CACHE_LIFETIME_USER, self::CACHE_KEY_USER . $user->getId())
            ->getResult();

As you can see in the example, we are fetching a user object, we are storing the result in Redis. Next time we come into this code, Doctrine is going to ask to Redis if the key we used to storage the user exists. If it does, we will not hit MySQL, just use Redis info. If we search a different username it will again use MySQL because the key will not exist in Redis.

That’s how you make the APP work as hell! try it and you will see the results.