Search code examples
luatarantoolnosql

Select from Tarantool by secondary index with sort by another field and limit/offset

I have some space top with fields:
-id,
-status,
-rating

I have two indexes for space top: 

--primary  
box.space.top:create_index('primary', { type = 'TREE', unique = true, parts = { 1, 'NUM' } })
--status
box.space.top:create_index('status', { type = 'TREE', unique = false, parts = { 2, 'NUM' } })

I can select by id or status

--select by id 
space.top.index.primary:select(someId) 
--select by status with limit/offset 
space.top.index.status:select({someStatus}, {iterator = box.index.EQ, offset = 0, limit = 20})

Sometimes i need select by status with ordering by rating.
What is the best way? Create another index with parts status, rating and make some tricky query if it`s possible? Or continue select by status and make sort by rating in Lua procedure? Thanks!

UPD: Thanks, Kostya! I modified index status like this:

box.space.top:create_index('status_rating', { type = 'TREE', unique = false, parts = { 2, 'NUM', 3 'NUM' } })

And now i can query:

local active_status = 1 
local limit = 20 
local offset = 0
box.space.top.index.status_rating:select({active_status}, {iterator = box.index.LE, offset=offset, limit=limit})

Great!

Solution

  • Doesn't make sense to create the third index, if you need to order by rating, just include it into the second index as the second part, and use GE/GT iterator, the data will come out ordered. This is an in-memory database, adding more parts to an index doesn't use up more memory, only slows down insertion a bit.