-1

So I have a table with the following data:

id                                   business_id   credit  name      owes     partner_id  type_id  updated

45b4bvfdghfghbdgfgfhbdfghbdbbfg       435634652     0       FORL      55      69992528       3  1652260271000
fghfdhbf657asdfsf43454356487768       435634652     0       FORL      77      69992528       3  1652529600000
fghfdhbf657a4sdf4365344565456487768   435634652     0       Hylde     65      69992528       2  1652529600000
fghfdhbf657a4564564565456487768       435634652     0       CC         5      69992528       1  1652529600000

And I am querying them based on the latest updated by type_id, partner_id and business_id. The query below is run on a loop:

 $result = $transaction_db->executeStatement([
    'Limit' => 1,
    'Statement' => 'SELECT * 
    FROM "items"."business_id-updated-index" 
    WHERE business_id = ' . $user_company_id . ' AND type_id = ' . intval($type['id']) . ' AND partner_id = ' . $data['cvr'] . ' 
   ORDER BY updated DESC',
]);

I receive only this row:

fghfdhbf657a4564564565456487768       435634652     0       CC         5      69992528

If I remove the limit it does fetch everything but thats not what I want. When this table grows to milions of items, thats not going to be a cheap thing to do, thats why I really need the LIMIT.

I do have GSI with sort keys as well: enter image description here

I am new to this, if I havent explained something clear enough please let me know.

Update:

As Charles has answered below, I tried adding a composite index by going to the AWS DynamoDB console and typing business_id#type_id#partner_id in the partition key and updated in the sort key. I named the index like this business_id-type_id-partner_id-updated-index but now it throws this error:

ValidationException: Must have at least one non-optional hash key condition in WHERE clause when using ORDER BY clause.

showtime
  • 1
  • 1
  • 17
  • 48
  • 1
    DDB really isn't designed for that, see [How to model data in dynamodb if your access pattern includes many WHERE conditions](https://stackoverflow.com/questions/71901071/how-to-model-data-in-dynamodb-if-your-access-pattern-includes-many-where-conditi) – Charles Jun 21 '22 at 17:19
  • @Charles but I am only using 3 where conditions. Is there a specific limit, how many can we use? – showtime Jun 21 '22 at 18:20
  • for efficiency, you can only have two. An equality on the PK and some comparison to the sort key. Unlike an RDBMS, DDB can not use more than a single index at once. – Charles Jun 21 '22 at 20:12

1 Answers1

0

In this particular case, since you are looking at equalities...

You could build a GSI with a PK that uses a composite value of business_id#type_id#partner_id

so for example 435634652#5#69992528

The sort key would be the updated attribute.

Charles
  • 21,637
  • 1
  • 20
  • 44