I am new to Redis and trying to implement a thing with PHP and Redis.
I am just describing the scenario in MySQL, I am not migrating anything from MySQL to Redis.
Let's say I have a table, in which I will be storing user's daily consumption, with the following structure
+-----------+------------+---------+
| user_id | date | count |
+-----------+------------+---------+
| 123 | 2017-06-06 | 231 |
| 123 | 2017-06-07 | 456 |
| 124 | 2017-06-06 | 433 |
| 124 | 2017-06-07 | 188 |
+-----------+------------+---------+
The count column would be updated in the following manner, with every call
UPDATE `table`
SET count = count + 12
WHERE user_id = 123 AND date = 2017-06-06
After reading about Redis, I understand that there is no concept of tables in Redis and to store such data, I should be using keys in the following format :
(e.g. key user:123:date:2017-06-06)
127.0.0.1:6379> INCRBY user:123:date:2017-06-06 12
(integer) 12
127.0.0.1:6379> INCRBY user:123:date:2017-06-06 32
(integer) 44
So far so good.
Now the issues I am facing is with querying. In MySQL, at the end of the month I can show users their consumption and billing by simply using the following query :
SELECT `date`,`count` FROM `table` WHERE `date` > 'some_date' AND `user_id` = 123
or
SELECT SUM(`count`),`user_id`,MONTH(date) GROUP BY `user_id`,MONTH(`date`)
But I am not sure how to do this in Redis, if I want to calculate for a single user, then it is easy as I know the format of key and have user_id, so I can create the key and fetch exact data I need
127.0.0.1:6379> GET user:123:date:2017-06-06 32
But is there something like or something similar
127.0.0.1:6379> GET user:*:date:*
Or is Redis not made for such type of queries and thing?
Any help would be appreciated. Thanks (or if you can direct me to some helpful documentation)
(Reason, I am implementing it in Redis and not MySQL is, there will be many increment queries per second, about hundreds and I thought Redis would be better for this. As the maximum time it will be only INCR query, and once or twice searching)
There are a few different ways you can store your data in redis that would help with querying.
If you stored each user in their own set, e.g.
HSET user:1234 date:2017-06-06 231
then it would be very easy to increment the counts:
HINCRBY user:1234 date:2017-06-06)
But more difficult to get dates by range (you'd need to retrieve the entire hash and then loop through it checking dates.)
If you stored each users date in a sorted set using epoch dates:
ZADD userdates:1234 1507317704 123
Then it would be easy to get a date range using ZRANGEBYSCORE, but harder to increment a score as you would need to retrieve the current score and then write it back.
You could also use a hybrid of the two, where you store dates in a sorted set, and use that to lookup dates in the hash. It depends on your data and if you know the dates in advance.