Laravel model accessor fetching from cache - performance enhancements

Question

I have a list of items in database and each item has option to be voted down or up. Those votes are stored in MySql along with other item fields. For example someting like this:

Schema::create('items', function ($table) {
    $table->increments('id');
    $table->text('message');
    $table->integer('up_votes')->unsigned()->default(0);
    $table->integer('down_votes')->unsigned()->default(0);
    $table->timestamps();
});

User can vote down/up every day. When user decides to vote I store his decision to memcached for one day and increase one of the fields (up_votes or down_votes) accordingly.

$voteKey = sprintf('%s-%s', $request->ip(), $item->id);

if (!Cache::has($voteKey)) {
    $vote = $request->get('vote');

    $this->item->increment($vote ? 'up_votes' : 'down_votes');
    Cache::put($voteKey, $vote, (60*24));
}

Next I want to have information about how certain user voted. I created accessor in model:

public function getVoteAttribute($value)
{
    $voteKey = sprintf('%s-%s', Request::ip(), $this->id);

    return $this->attributes['vote'] = Cache::get($voteKey);
}

protected $appends = ['vote'];

Is this smart to do or could there be some performance issues with long lists? If 100 items are returned, there are 100 connections to memcached per user. How can I improve this or is this something I should not much worry about because cache server can handle this amount of connections with no problem.

Answer 1

Current Use of Cache & DB

1. You're using IP address to identify users v/s something as simple as user_id. Is this intentional? If the same user logs in again from another IP, would you want to show a different number?

2. In the DB, you're storing # up-votes & down-votes per item but in the cache you're storing type of vote (up-vote/down-vote) by combination of item and IP address (or user id). Also, the cache expires after 24 hours.

   So, when you say Cache::get($voteKey) it will return either up-vote or down-vote but only if the user voted on this item in the last 24 hours (otherwise returns null). Is that intended?

When to use Cache v/s DB

Typically you would use cache for frequent queries (when you need to perform a particular read op frequently but write not as frequently). If this isn't the case, you would generally fallback to DB.

Now let's say you actually wanted to store both # up-votes/down-votes by item and type of vote by combination of user and item. Think about it for a second, which query would be more frequent? # up-votes / down-votes per item or type of vote by combination of user & item? Of course, it would be the first scenario (if at all). However, you're doing quite the reverse.

You're storing the more frequently accessed query in DB and the less frequently accessed query in cache

This will actually reduce the overall performance of your app!

What would be the right way?

Well, that depends on the use case. For instance, let's say you want to store both the user id and type of vote by item id (typical use case because you wouldn't want any user's vote to be counted more than once per item on a vote recast). Then, I would go about storing this in the DB and storing the total # up-votes/down-votes by item in the cache (only if frequently accessed - so for instance, you may choose not to store the # votes for all items but only for the more popular items with at least X number of views)

For the above use case, I would suggest something like this:

DB Schema

Schema::create('item_user', function ($table) {
    $table->increments('id');
    $table->integer('user_id')->unsigned();
    $table->integer('item_id')->unsigned();
    $table->enum('vote_type', ['up_vote', 'down_vote']);
    $table->unique(['user_id', 'item_id']);
    $table->timestamps();
});

Vote Controller Logic

$user = Auth::user();
$vote = $request->get('vote');
$voteType = $vote ? 'up_vote' : 'down_vote';
$voteKey = "{$voteType}_{$item->id}";

$item->users()->updateExistingPivot($user->id, ['vote_type' => $voteType]);
Cache::increment($voteKey);

Original Question

As to your original question, Laravel uses a single connection instance for cache queries for both Redis and Memcached. So, if the same request fetches 100 different cache items, it would not initiate 100 connections - it would do the job in a single cache connection