title: How to retrieve records efficiently using indices in dynamodb aws
body: <p>I want to fetch records of all male or female students from <em>class 5 to class 11</em> who have secured between <strong>x1</strong> and <strong>x2</strong> marks in maths, between <strong>y1</strong> and <strong>y2</strong> marks in science, between <strong>z1</strong> and <strong>z2</strong> marks in english and between <strong>w1</strong> and <strong>w2</strong> marks in social...</p>
  <p>My model looks as follows:</p>
  <pre><code>type StudentMarks
      @model
      @key(
          name: &quot;filterbyClassAndMarks&quot;
          fields: [
              &quot;gender&quot;
              &quot;classCode&quot;
              &quot;mathsMarks&quot;
              &quot;socialMarks&quot;
              &quot;englishMarks&quot;
              &quot;scienceMarks&quot;
          ]
          queryField: &quot;filterbyClassAndMarks&quot;
      )
      @auth(
          rules: [
              { allow: private, provider: iam, operations: [read] }
              { allow: public, provider: iam, operations: [read] }
          ]
      ) {
      id: ID!
      name: String
      gender: String
      classCode: String
      mathsMarks: String
      socialMarks: String
      englishMarks: String
      scienceMarks: String
  }
  </code></pre>
  <p>The GSI created with gender as the partition key(hashkey) and compound sort/range key with classCode, mathMarks, socialMarks, scienceMarks, englishMarks fields <a href="https://i.stack.imgur.com/5lwUd.png" rel="nofollow noreferrer">is showing item count as 0.</a></p>
  <p>I am trying to query using the following graphql:</p>
  <pre><code>    const listOfStudents = await API.graphql({
                  query: queries.filterbyClassAndMarks,
                  variables: {
                      gender: &quot;m&quot;,
                      classCodeMathsMarksSocialMarksEnglishMarksScienceMarks: {
                          between: [
                              {
                                  classCode: &quot;05&quot;,
                                  mathsMarks: &quot;06&quot;,
                                  scienceMarks: &quot;07&quot;,
                                  englishMarks: &quot;04&quot;,
                                  socialMarks: &quot;05&quot;,
                              },
                              {
                                  classCode: &quot;11&quot;,
                                  mathsMarks: &quot;90&quot;,
                                  scienceMarks: &quot;91&quot;,
                                  englishMarks: &quot;95&quot;,
                                  socialMarks: &quot;92&quot;,
                              },
                          ],
                      },
                  },
                  authMode: &quot;AWS_IAM&quot;,
              });
  </code></pre>
  <p><a href="https://i.stack.imgur.com/p9UJe.png" rel="nofollow noreferrer">This table</a> with 11 records should return 4 records as shown in green</p>
  <p>The cloud watch logs are as below:</p>
  <pre><code>{
      &quot;logType&quot;: &quot;RequestMapping&quot;,
      &quot;path&quot;: [
          &quot;filterbyClassAndMarks&quot;
      ],
      &quot;fieldName&quot;: &quot;filterbyClassAndMarks&quot;,
      &quot;resolverArn&quot;: &quot;arn:aws:appsync:ap-south-1:488377001042:apis/4tbr6xolzjfctl5tdbiur7jqnu/types/Query/resolvers/filterbyClassAndMarks&quot;,
      &quot;requestId&quot;: &quot;5de45e66-1a5d-44dd-80cc-1a5b93a3c3aa&quot;,
      &quot;context&quot;: {
          &quot;arguments&quot;: {
              &quot;gender&quot;: &quot;m&quot;,
              &quot;classCodeMathsMarksSocialMarksEnglishMarksScienceMarks&quot;: {
                  &quot;between&quot;: [
                      {
                          &quot;classCode&quot;: &quot;05&quot;,
                          &quot;mathsMarks&quot;: &quot;06&quot;,
                          &quot;socialMarks&quot;: &quot;05&quot;,
                          &quot;englishMarks&quot;: &quot;04&quot;,
                          &quot;scienceMarks&quot;: &quot;07&quot;
                      },
                      {
                          &quot;classCode&quot;: &quot;11&quot;,
                          &quot;mathsMarks&quot;: &quot;90&quot;,
                          &quot;socialMarks&quot;: &quot;92&quot;,
                          &quot;englishMarks&quot;: &quot;95&quot;,
                          &quot;scienceMarks&quot;: &quot;91&quot;
                      }
                  ]
              },
          },
          &quot;stash&quot;: {},
          &quot;outErrors&quot;: []
      },
      &quot;fieldInError&quot;: false,
      &quot;errors&quot;: [],
      &quot;parentType&quot;: &quot;Query&quot;,
      &quot;graphQLAPIId&quot;: &quot;4tbr6xolzjfctl5tdbiur7jqnu&quot;,
      &quot;transformedTemplate&quot;: &quot;{\&quot;version\&quot;:\&quot;2018-05-29\&quot;,\&quot;operation\&quot;:\&quot;Query
  \&quot;,\&quot;limit\&quot;:100,\&quot;query\&quot;:{\&quot;expression\&quot;:\&quot;#gender = :gender AND #sortKey 
  BETWEEN :sortKey0 AND :sortKey1\&quot;,\&quot;expressionNames\&quot;:{\&quot;#gender\&quot;:\&quot;gender
  \&quot;,\&quot;#sortKey\&quot;:\&quot;classCode#mathsMarks#socialMarks#englishMarks#scienceMarks
  \&quot;},\&quot;expressionValues\&quot;:{\&quot;:gender\&quot;:{\&quot;S\&quot;:\&quot;m\&quot;},\&quot;:sortKey0\&quot;:{\&quot;S
  \&quot;:\&quot;05#06#05#04#07\&quot;},\&quot;:sortKey1\&quot;:{\&quot;S\&quot;:\&quot;11#90#92#95#91\&quot;}}},\&quot;index
  \&quot;:\&quot;filterbyClassAndMarks\&quot;,\&quot;scanIndexForward\&quot;:true}&quot;
  
  }
  </code></pre>
  <p>It is showing the scanned count as 0 in the result:</p>
  <pre><code> &quot;result&quot;: {
              &quot;items&quot;: [],
              &quot;scannedCount&quot;: 0
          },
  </code></pre>
  <p>And the final response is success with empty result as shown below:</p>
  <pre><code>Object { data: {…} }
  ​
  data: Object { filterbyClassAndMarks: {…} }
  ​​
  filterbyClassAndMarks: Object { items: [], nextToken: null }
  ​​​
  items: Array []
  ​​​​
  length: 0
  ​​​​
  &lt;prototype&gt;: Array []
  ​​​
  nextToken: null
  
  Array []
  </code></pre>
  <p>Key conditions on compound sort keys is possible <a href="https://aws.amazon.com/blogs/database/how-to-use-dynamodb-global-secondary-indexes-to-improve-query-performance-and-reduce-costs/" rel="nofollow noreferrer">as per aws documentation</a>... I do not understand what I am missing exactly. Probably the GSI index is not properly configured/written.</p>
  <p>Since most of the time the result is less than 1% of records in the table and is read intensive, scanning/reading all the records and filtering them is a very naive solution. Need better solution either with indices or otherwise.</p>
  <p>SECOND EDIT:<br />
  <a href="https://i.stack.imgur.com/Zg2r2.png" rel="nofollow noreferrer">Example of expected behavior of applying hash key and compound sort key before filtering.</a>. The example is given to highlight expected behavior only and does not indicate approximate percentage of records not read due to hash key or compound sort key.</p>