title: Cloud Spanner is using a secondary index when it should not
body: <p>An existing query that performed quickly using the primary key massively slowed down (10ms -> 8sec) without notice because a secondary index that has been created for another use-case is now used automatically.</p>
  
  <p>The 'Explanation' of the Cloud-Spanner-Web-Query tells me that the secondary index is used. If I change ordering (just for testing purpose) or supply FORCE_INDEX the query is quick again.</p>
  
  <p>I can 'fix' this problem by using <strong>FORCE_INDEX=_BASE_TABLE</strong> which is documented in the <a href="https://cloud.google.com/spanner/docs/query-syntax#syntax" rel="nofollow noreferrer">Cloud Spanner Query Syntax Documentation</a>.</p>
  
  <p><strong>My question is: Do I really have to do this for every query to avoid such effects ?</strong></p>
  
  <p>This mixes query-definition with index-definition which is not a good thing IMHO.</p>
  
  <p>Table with primary index:</p>
  
  <pre><code>CREATE TABLE change_history (
      userId INT64 NOT NULL,
      createdAtUnique INT64 NOT NULL,
      itemId STRING(512) NOT NULL,
      newValue FLOAT64 NOT NULL,
      oldValue FLOAT64 NOT NULL,
  ) PRIMARY KEY (userId, itemId, createdAtUnique DESC)
  </code></pre>
  
  <p>Secondary index:</p>
  
  <pre><code>CREATE INDEX ch_userid_createdatunique_all ON change_history (
      userId,
      createdAtUnique
  ) STORING (
      newValue,
      oldValue
  )
  </code></pre>
  
  <p>Original query:</p>
  
  <pre><code>SELECT * FROM change_history WHERE                         
      userId = 2563
      AND itemId = "215414"
      AND createdAtUnique &gt;= 15385766670000000
      AND createdAtUnique &lt;= 15465254670000000 ORDER BY createdAtUnique
  </code></pre>
  
  <p>I expected the query to keep using the primary key which it was designed for.</p>
  
  <p>But by adding the secondary index the query started to use this one instead of the primary key.</p>