0

I working on web application which uses mysql as backend and redis as caching server and i stored the records in mysql database as well as redis(using predis library).

During fetching the records from database,first check whether the key present(primary key i.e id) in redis,if "yes" get data directly from redis,otherwise hit the database for data.

Suppose database tables are posts,comments.

During storing data for posts,comments details,used posts,comments tables for storing respective data.

Comments table having foreign key of post table called post_id

At redis server,used hash key (id) both for posts,comments and but different fields for post,comments like post_{id} for post, comment_{id} for comment and stored values using hset,sadd redis commands in allposts,allcomments sets.

It is easy to perform join operation using normal sql query to fetch related data.

Is there any solutions for fetching related data in redis or predis ?

Aniket Muruskar
  • 267
  • 3
  • 9

2 Answers2

0

You can use prefixes for related data, such as group1_yourhashedkey, group2_yourhashedkey... etc. You cannot do joins on Redis. Redis is a cache server, not a sql server.

http://redis.io/commands/KEYS

I would also suggest not using primary key from sql as key, because you are using more than one table and most probably, you will have duplicate numeric id's (specially if you use autoincrement tables). Make a md5 has with something unique.

peixotorms
  • 1,246
  • 1
  • 10
  • 21
0

Well, there are ways to do "join" in redis, but they are not worth of effort. Especially when you are using redis as proxy cache:

During fetching the records from database,first check whether the key present(primary key i.e id) in redis,if "yes" get data directly from redis,otherwise hit the database for data.

You probably already have function like: 

function getDataByIds($table, $ids)

where $table is 'posts' or 'comments' etc, and $ids are searched keys.

Make it fast ( use http://redis.io/commands/hmget for loading, use "SELECT * FROM commments WHERE id IN (1,2,3,4,..)" for loading missing keys in one query, use pipelining for setting in redis loaded keys)

Then just manually load foreign keys:

$comments = getDataByIds('comments', $commentsIds);
$postsIds = array_column($comments,'post_id');
$posts = getDataByIds('posts', $postsIds); 
foreach($comments as &$comment) {
    $comment['post'] = $posts[$comment['post_id']];
};

If you like to do joins server side, you can try:

http://redis.io/commands/SORT

SORT mylist BY weight_*->fieldname GET object_*->fieldname

or using lua scripts.

my-nick
  • 691
  • 1
  • 5
  • 11