4

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!

Community
  • 1
  • 1
Levintsev Aleksandr
  • 112
  • 1
  • 7
  • Good advise requires more information about your data. What average number of rows each status has? Will you read all pages or only first one? – dgreen Jan 08 '16 at 20:05
  • The average number of rows is about 2000 - 3000 units. I`m going to use limit/offset options for paginations – Levintsev Aleksandr Jan 09 '16 at 11:16

2 Answers2

3

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.

Kostja
  • 1,607
  • 10
  • 17
1

Call with GE/LE iterator and partial index may work not as expected than there is no matching tuples or limit is too high.

Suppose, we have following tuples (status, rating):

{ 1, 1 }
{ 3, 1 }
{ 3, 2 }

Than call 

box.space.top.index.status_rating:select({2}, {iterator = box.index.GE, limit=1})

will return tuple {3, 1} as it greater than {2}

And call 

box.space.top.index.status_rating:select({1}, {iterator = box.index.GE, limit=2})

will return two tuples {1, 1}, {3, 1}

In both case tuple {3, 1} may be not expected

D.Smal
  • 23
  • 3