Currently the table is saved as TSV (tab-separated vector) format in my hard disk, 432MB in size. I want to populate the table into Redis in order to complete this kind of query most efficiently: Given a min value and a max value for each column, count the number of rows that are within the given range, i.e.

1) How to populate the table into Redis? What kind of key/value data structure should I use? Hashes, lists, sets, sorted sets, or what else?

2) After populating the table, given 9 min and max values for the 9 columns, how to write the query in order to get the count, i.e. number of rows falling within the 9 ranges? One way I can think of is, first find out the rows that satisfy (min_colX <= colX <= max_colX) for each X in 1 to 9, and then calculate their intersection. But I guess this is not the most efficient way. I just want to retrieve the count as fast as possible.

By the way, I have tried MongoDB. It is straightforward to populate the table using mongoimport, but it takes 10 seconds to complete my query, which is too slow and not acceptable for my real-time application. In contrast, Redis holds data in memory, so I hope Redis can shorten the query time to 1 second.

how did you run the MongoDB query? Did you create any indexes? It doesn't seem like too much data - it would all fit in memory with Mongo as well.
–
Asya KamskyJun 10 '12 at 2:14

1

With Redis, you have no table, no column, no query. Indexing things in multiple dimensions is not possible (not directly). To support efficient queries in n dimensions, you need a specific data structure like a kd-tree -en.wikipedia.org/wiki/Kd-tree
–
Didier SpeziaJun 10 '12 at 7:20