[Feature Request] ZINTERRANGE

[GoogleCodeExporter]

ZINTERRANGE key1 min max ... keyN min max ORDER key1 LIMIT offset limit

returns, an ordered by key1's scores list of values, some zset's can be 
without min max condition.

Use case: SQL: select object_id ..... where param1 between .. and .. and 
param2 between .. and .. order by param1 limit 90000,100.

It would by also great, if it can support not just range conditions but 
also IN conditions: ... key1 min max key2 IN score1 score2 score3 ....
SQL: ... key2 in (score1,score2,score3) ...

Original issue reported on code.google.com by dennis.f...@gmail.com on 17 Mar 2010 at 6:39

[GoogleCodeExporter]

Why would Redis need to implement SQL alike behavior? Could you give a more 
concrete use case?

Original comment by pcnoordh...@gmail.com on 18 Mar 2010 at 8:48

[GoogleCodeExporter]

Hello,
We trying to think that redis can be a complete replace of the database, in the 
next 
generation of our service, but if it only had a few more features. We like the 
way 
redis is done, and the way it moves: appendonlylog, zsets, vm. In our case we 
have to 
store objects with arbitrary params, and then do filtering and sort by some of 
them 
and do pagination of results.
Real life use case: Real Estate Portal - filter by price and area and then sort 
everything by price and bring to user with pagination. e-shop - filter products 
by 
price range, by category and arbitrary params inside the category, and then 
sort it 
by price1 price2 etc and bring to user with pagination. In this point of view 
we 
think zsets can be used like indexes in db: store objects params as key-value 
and for 
the filtering and sorting params - create zsets (score:param_value, 
value:object_id). 

Original comment by dennis.f...@gmail.com on 19 Mar 2010 at 8:46

[GoogleCodeExporter]

Hello,
It was very interesting to see, how would redis perform if it had such 
features(to 
see the power of indexed skiplists), so i could'n stop myself and wrote two new 
commands:

ZQUERY numberOfzsets key1 [RANGE minScore maxScore|IN numberOfINArguments 
score1 
scrore2 .. scoreN] key2 [RANGE minScore maxScore|IN numberOfINArguments score1 
scrore2 .. scoreN] ... [WITHSCORES] [LIMIT offset count]

ZQUERYCOUNT numberOfzsets key1 [RANGE minScore maxScore|IN numberOfINArguments 
score1 
scrore2 .. scoreN] key2 [RANGE minScore maxScore|IN numberOfINArguments score1 
scrore2 .. scoreN] ...

both commands can work with one or greater zset's with or without RANGE or IN 
conditions

ZQUERY performs intersection of the elements of the zsets applying range and in 
conditions to zsets, and returns values or values with scores ORDERED BY FIRST 
ZSET 
SCORE, so result's are always sorted by first zset

It works great and performance is good. If this functionality is interesting to 
anybody, i can do performance comparison tests with mysql, sphinxsearch and 
mongodb.
What do you think is there any chance, that this or similar functionality can 
be 
addopted into redis? My opinion that redis can be used in such way and perform 
well  

P.S.1 When i was thinking about command syntax there was another idea:
ZINTERRANGE key1 [RANGES min1 max1 min2 max2 min3 max3...] ... keyN [RANGES 
min1 max1 
min2 max2 min3 max3...] WITHSCORES LIMIT offset count - maybe this syntax is 
more 
redis like
P.S.2 I am ready to do some coding and refactoring 

Original comment by dennis.f...@gmail.com on 30 Mar 2010 at 11:47

Attachments:

• redis.c.1.3.8.patch
• code.fragments.c

[GoogleCodeExporter]

The problem with this in my opinion is that there is no efficient way to 
perform this kind of tasks, mathematically speaking... Redis is trying to force 
the user to take a different approach, for example to make the "filtering" part 
of the query discrete, using multiple sorted sets.

What's cool about sorted sets is that you can have million of elements and 
still add/del/update operations, as well as ZRANGE, will take a fraction of 
millisecond to run. With this instead feature instead we are going to have slow 
queries... if the user is willing to mount slow queries it should do it using 
temp keys and multiple ZINTERSTORE operations IMHO. Combining this commands 
should be possible to get the same effects with very similar performances...

Leaving this issue open for a few weeks to see if we get some comment,
Salvatore

Original comment by anti...@gmail.com on 27 Aug 2010 at 11:00

[GoogleCodeExporter]

Original comment by anti...@gmail.com on 31 Aug 2010 at 10:52

• Added labels: Type-Enhancement
• Removed labels: Type-Defect

[GoogleCodeExporter]

on the other hand, removing duplicates from the union is the exact complement 
of what's needed in an intersection. I don't think it's any more mathematically 
complicated than that. :)

Original comment by karthikk...@gmail.com on 15 Jun 2011 at 6:07