Secondary Indexes

A secondary index is helpful for quickly looking up data when searching by one or more non-key columns.

For example, you don't have to do anything special to index the primary key column of Singers because it's automatically indexed for you. You can look up values of the FirstName column for a range of SingerIds in the Singers table. However, if you also want to be able to quickly look up a set of SingerIds for a given range of FirstName and LastName values without having to scan the whole table, you should create a secondary index on the Singers table.

Creating a secondary index

Use the CREATE INDEX statement to define a secondary index in your schema. Here are some examples:

To index all Singers in the database by their first and last name:

CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName)

To create an index of all Songs in the database by the value of SongName:

CREATE INDEX SongsBySongName ON Songs(SongName)

To index only the songs for a particular singer, interleave the index in the table Singers:

CREATE INDEX SongsBySingerSongName ON Songs(SingerId, SongName),
    INTERLEAVE IN Singers

To index only the songs on a particular album:

CREATE INDEX SongsBySingerAlbumSongName ON Songs(SingerId, AlbumId, SongName),
    INTERLEAVE IN Albums

To index by descending order of SongName:

CREATE INDEX SongsBySingerAlbumSongNameDesc ON Songs(SingerId, AlbumId, SongName DESC),
    INTERLEAVE IN Albums

Note that the DESC annotation above applies only to SongName. To index by descending order of other index keys, annotate them with DESC as well: SingerId DESC, AlbumId DESC.

Index creation can take from several minutes to many hours. The time needed to create a secondary index depends on:

  • the size of the data set
  • the number of nodes in the instance
  • the load on the instance

You can cancel a long-running index creation operation using the projects.instances.databases.operations.cancel method.

Query using indexes

Suppose you added a MarketingBudget column to the Albums table and created an index AlbumsByAlbumTitle:

CREATE TABLE Albums (
  SingerId         INT64 NOT NULL,
  AlbumId          INT64 NOT NULL,
  AlbumTitle       STRING(MAX),
  MarketingBudget  INT64,
) PRIMARY KEY (SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle);

To read data using this index, you can either execute a SQL query statement or use Cloud Spanner's read interface. The more common way to fetch data from this index is to use the SQL query interface, which lets you query data from any column of the table that's indexed.

Index directive

If you want Cloud Spanner to query a table MyTable using the index MyTableIndex, you must indicate the index name using an index directive in the SQL statement. (Cloud Spanner chooses an index automatically only in rare circumstances. In particular, Cloud Spanner does not automatically choose a secondary index if the query requests any columns that are not stored in the index.) The syntax for the index directive is:

FROM MyTable@{FORCE_INDEX=MyTableIndex}

Example SQL query statement that uses an index directive:

SELECT AlbumId, AlbumTitle, MarketingBudget
FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}
WHERE SingerId = 1 AND AlbumTitle >= 'Aardvark' AND AlbumTitle < 'Goo'

Notes on using index directives:

  • All indexes in Cloud Spanner store all key columns from the base table, all columns indexed, and all columns specified in the optional STORING clause of the index definition.
  • For SQL queries that use an index directive, Cloud Spanner's SQL query processor might need to read columns that are required by the query but that aren't stored in the index. The query processor retrieves these columns using a join between the index and the base table.
    • For example, in the definition of the AlbumsByAlbumTitle index above, the MarketingBudget column is not stored in the index, but is one of the selected columns in the SQL query on that index. To fetch this column, Cloud Spanner does a lookup of the MarketingBudget column from the base table under the hood and joins it with data from the index to return the query results. You can avoid this extra join by storing the MarketingBudget column in the index; see STORING clause below.
  • Index directives are not hints, which means that an error will be raised if:

Using secondary indexes in SQL queries can help speed up common queries, as described in SQL Best Practices. The following code snippets show how to write and execute queries that fetch the values of AlbumId, AlbumTitle, and MarketingBudget using the index AlbumsByAlbumTitle:

C#

string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
    var cmd = connection.CreateSelectCommand(
        "SELECT AlbumId, AlbumTitle, MarketingBudget FROM Albums@ "
        + "{FORCE_INDEX=AlbumsByAlbumTitle} "
        + $"WHERE AlbumTitle >= @startTitle "
        + $"AND AlbumTitle < @endTitle",
        new SpannerParameterCollection {
            {"startTitle", SpannerDbType.String},
            {"endTitle", SpannerDbType.String} });
    cmd.Parameters["startTitle"].Value = startTitle;
    cmd.Parameters["endTitle"].Value = endTitle;
    using (var reader = await cmd.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine("AlbumId : "
            + reader.GetFieldValue<string>("AlbumId")
            + " AlbumTitle : "
            + reader.GetFieldValue<string>("AlbumTitle")
            + " MarketingBudget : "
            + reader.GetFieldValue<string>("MarketingBudget"));
        }
    }
}

Go

func queryUsingIndex(ctx context.Context, w io.Writer, client *spanner.Client) error {
	stmt := spanner.Statement{
		SQL: `SELECT AlbumId, AlbumTitle, MarketingBudget
			FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}
			WHERE AlbumTitle >= @start_title AND AlbumTitle < @end_title`,
		Params: map[string]interface{}{
			"start_title": "Aardvark",
			"end_title":   "Goo",
		},
	}
	iter := client.Single().Query(ctx, stmt)
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		var albumID int64
		var marketingBudget spanner.NullInt64
		var albumTitle string
		if err := row.ColumnByName("AlbumId", &albumID); err != nil {
			return err
		}
		if err := row.ColumnByName("AlbumTitle", &albumTitle); err != nil {
			return err
		}
		if err := row.ColumnByName("MarketingBudget", &marketingBudget); err != nil {
			return err
		}
		budget := "NULL"
		if marketingBudget.Valid {
			budget = strconv.FormatInt(marketingBudget.Int64, 10)
		}
		fmt.Fprintf(w, "%d %s %s\n", albumID, albumTitle, budget)
	}
	return nil
}

Java

static void queryUsingIndex(DatabaseClient dbClient) {
  ResultSet resultSet =
      dbClient
          .singleUse()
          .executeQuery(
              // We use FORCE_INDEX hint to specify which index to use. For more details see
              // https://cloud.google.com/spanner/docs/query-syntax#from-clause
              Statement.of(
                  "SELECT AlbumId, AlbumTitle, MarketingBudget\n"
                      + "FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}\n"
                      + "WHERE AlbumTitle >= 'Aardvark' AND AlbumTitle < 'Goo'"));
  while (resultSet.next()) {
    System.out.printf(
        "%d %s %s\n",
        resultSet.getLong("AlbumId"),
        resultSet.getString("AlbumTitle"),
        resultSet.isNull("MarketingBudget") ? "NULL" : resultSet.getLong("MarketingBudget"));
  }
}

Node.js

// Imports the Google Cloud client library
const Spanner = require('@google-cloud/spanner');

// Instantiates a client
const spanner = Spanner();

// Uncomment these lines to specify the instance and database to use
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Uncomment these lines to specify the start and end title(s)
// const startTitle = 'Ardvark';
// const endTitle = 'Goo';

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const query = {
  sql: `SELECT AlbumId, AlbumTitle, MarketingBudget
        FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}
        WHERE AlbumTitle >= @startTitle AND AlbumTitle <= @endTitle`,
  params: {
    startTitle: startTitle,
    endTitle: endTitle
  }
};

// Queries rows from the Albums table
database.run(query)
  .then((results) => {
    const rows = results[0];

    rows.forEach((row) => {
      const json = row.toJSON();
      const marketingBudget = json.MarketingBudget ? json.MarketingBudget.value : null; // This value is nullable
      console.log(`AlbumId: ${json.AlbumId.value}, AlbumTitle: ${json.AlbumTitle}, MarketingBudget: ${marketingBudget}`);
    });
  });

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Queries sample data from the database using SQL and an index.
 *
 * The index must exist before running this sample. You can add the index
 * by running the `add_index` sample or by running this DDL statement against
 * your database:
 *
 *     CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)
 *
 * Example:
 * ```
 * query_data_with_index($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 * @param string $startTitle The start of the title index.
 * @param string $endTitle   The end of the title index.
 */
function query_data_with_index($instanceId, $databaseId, $startTitle, $endTitle)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $parameters = [
        'startTitle' => $startTitle,
        'endTitle' => $endTitle
    ];

    $results = $database->execute(
        'SELECT AlbumId, AlbumTitle, MarketingBudget ' .
        'FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle} ' .
        'WHERE AlbumTitle >= @startTitle AND AlbumTitle < @endTitle',
        ['parameters' => $parameters]
    );

    foreach ($results as $row) {
        printf('AlbumId: %s, AlbumTitle: %s, MarketingBudget: %d' . PHP_EOL,
            $row['AlbumId'], $row['AlbumTitle'], $row['MarketingBudget']);
    }
}

Python

def query_data_with_index(
        instance_id, database_id, start_title='Aardvark', end_title='Goo'):
    """Queries sample data from the database using SQL and an index.

    The index must exist before running this sample. You can add the index
    by running the `add_index` sample or by running this DDL statement against
    your database:

        CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)

    This sample also uses the `MarketingBudget` column. You can add the column
    by running the `add_column` sample or by running this DDL statement against
    your database:

        ALTER TABLE Albums ADD COLUMN MarketingBudget INT64

    """
    from google.cloud.proto.spanner.v1 import type_pb2

    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    params = {
        'start_title': start_title,
        'end_title': end_title
    }
    param_types = {
        'start_title': type_pb2.Type(code=type_pb2.STRING),
        'end_title': type_pb2.Type(code=type_pb2.STRING)
    }

    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            "SELECT AlbumId, AlbumTitle, MarketingBudget "
            "FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle} "
            "WHERE AlbumTitle >= @start_title AND AlbumTitle < @end_title",
            params=params, param_types=param_types)

        for row in results:
            print(
                u'AlbumId: {}, AlbumTitle: {}, '
                'MarketingBudget: {}'.format(*row))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

sql_query = 'SELECT AlbumId, AlbumTitle, MarketingBudget
             FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}
             WHERE AlbumTitle >= "Aardvark" AND AlbumTitle < "Goo"'

client.execute(sql_query).rows.each do |row|
  puts "#{row[:AlbumId]} #{row[:AlbumTitle]} #{row[:MarketingBudget]}"
end

Read using indexes

Another way to fetch data from an index is to use Cloud Spanner's interface for executing a read using an index. For example, the following code snippet fetches the values of AlbumId and AlbumTitle using the index AlbumsByAlbumTitle:

C#

The code to read data using an index is the same as the previous sample for querying using indexes.

string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
    var cmd = connection.CreateSelectCommand(
        "SELECT AlbumId, AlbumTitle, MarketingBudget FROM Albums@ "
        + "{FORCE_INDEX=AlbumsByAlbumTitle} "
        + $"WHERE AlbumTitle >= @startTitle "
        + $"AND AlbumTitle < @endTitle",
        new SpannerParameterCollection {
            {"startTitle", SpannerDbType.String},
            {"endTitle", SpannerDbType.String} });
    cmd.Parameters["startTitle"].Value = startTitle;
    cmd.Parameters["endTitle"].Value = endTitle;
    using (var reader = await cmd.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine("AlbumId : "
            + reader.GetFieldValue<string>("AlbumId")
            + " AlbumTitle : "
            + reader.GetFieldValue<string>("AlbumTitle")
            + " MarketingBudget : "
            + reader.GetFieldValue<string>("MarketingBudget"));
        }
    }
}

Go

func readUsingIndex(ctx context.Context, w io.Writer, client *spanner.Client) error {
	iter := client.Single().ReadUsingIndex(ctx, "Albums", "AlbumsByAlbumTitle", spanner.AllKeys(),
		[]string{"AlbumId", "AlbumTitle"})
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var albumID int64
		var albumTitle string
		if err := row.Columns(&albumID, &albumTitle); err != nil {
			return err
		}
		fmt.Fprintf(w, "%d %s\n", albumID, albumTitle)
	}
}

Java

static void readUsingIndex(DatabaseClient dbClient) {
  ResultSet resultSet =
      dbClient
          .singleUse()
          .readUsingIndex(
              "Albums",
              "AlbumsByAlbumTitle",
              KeySet.all(),
              Arrays.asList("AlbumId", "AlbumTitle"));
  while (resultSet.next()) {
    System.out.printf("%d %s\n", resultSet.getLong(0), resultSet.getString(1));
  }
}

Node.js

// Imports the Google Cloud client library
const Spanner = require('@google-cloud/spanner');

// Instantiates a client
const spanner = Spanner();

// Uncomment these lines to specify the instance and database to use
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const albumsTable = database.table('Albums');

const query = {
  columns: ['AlbumId', 'AlbumTitle'],
  keySet: {
    all: true
  },
  index: 'AlbumsByAlbumTitle'
};

// Reads the Albums table using an index
albumsTable.read(query)
  .then((results) => {
    const rows = results[0];

    rows.forEach((row) => {
      const json = row.toJSON();
      console.log(`AlbumId: ${json.AlbumId.value}, AlbumTitle: ${json.AlbumTitle}`);
    });
  });

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Reads sample data from the database using an index.
 *
 * The index must exist before running this sample. You can add the index
 * by running the `add_index` sample or by running this DDL statement against
 * your database:
 *
 *     CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)
 *
 * Example:
 * ```
 * read_data_with_index($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function read_data_with_index($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $keySet = $spanner->keySet(['all' => true]);
    $results = $database->read(
        'Albums',
        $keySet,
        ['AlbumId', 'AlbumTitle'],
        ['index' => 'AlbumsByAlbumTitle']
    );

    foreach ($results->rows() as $row) {
        printf('AlbumId: %s, AlbumTitle: %s' . PHP_EOL,
            $row['AlbumId'], $row['AlbumTitle']);
    }
}

Python

def read_data_with_index(instance_id, database_id):
    """Reads sample data from the database using an index.

    The index must exist before running this sample. You can add the index
    by running the `add_index` sample or by running this DDL statement against
    your database:

        CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)

    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    with database.snapshot() as snapshot:
        keyset = spanner.KeySet(all_=True)
        results = snapshot.read(
            table='Albums',
            columns=('AlbumId', 'AlbumTitle'),
            keyset=keyset,
            index='AlbumsByAlbumTitle')

        for row in results:
            print('AlbumId: {}, AlbumTitle: {}'.format(*row))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

result = client.read "Albums", [:AlbumId, :AlbumTitle],
                     index: "AlbumsByAlbumTitle"

result.rows.each do |row|
  puts "#{row[:AlbumId]} #{row[:AlbumTitle]}"
end

Notes:

  • Unlike the previous example that executed a SQL query statement, this example uses Cloud Spanner's read interface, which does not support the ability to join an index with a data table to look up values that are not stored in the index. This means you can't read the column MarketingBudget using the index in the above example. If you want to be able to read the value of that column, you can either add the clause STORING (MarketingBudget) to the index definition as shown below, or do a manual read on the Albums table using the primary key values returned from the index read.
  • By default, the values that are read from the index are returned in ascending sort order by index key. If you wanted the values to be returned in descending order by index key:
    1. Annotate the index key with DESC:
        CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle DESC)
      • Note that the DESC annotation applies to a single index key. If there were more than one key in the list, and you wanted the values to be returned in descending order of all keys, you would annotate each key with DESC.
    2. If you're executing a read on this index with a KeyRange, the range must also use descending sort order (i.e. the value of the start key should be greater than the value of the end key).

STORING clause

As mentioned above, you can optionally store a copy of a column in the index using the STORING clause, which provides advantages for queries and read calls using the index (at the cost of extra storage):

  • SQL queries that use the index and select columns stored in the STORING clause do not require an extra join to the base table.
  • Read calls that use the index can read columns stored in the STORING clause.

For example, suppose you created an alternate version of AlbumsByAlbumTitle that stores a copy of the MarketingBudget column in the index (note the STORING clause in bold):

CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget)

Now when you select the MarketingBudget column using an index directive query with AlbumsByAlbumTitle2, the SQL processor reads the column directly from the index, rather than joining the index with the base table and retrieving the column from the base table using the table key.

Also, now you can read the MarketingBudget column in the read call directly, rather than having to manually look it up from the base table:

C#

Read data using the storing index by executing a query that explicitly specifies the index:

string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
    var cmd = connection.CreateSelectCommand(
        "SELECT AlbumId, AlbumTitle, MarketingBudget FROM Albums@ "
        + "{FORCE_INDEX=AlbumsByAlbumTitle2} "
        + $"WHERE AlbumTitle >= @startTitle "
        + $"AND AlbumTitle < @endTitle",
        new SpannerParameterCollection {
            {"startTitle", SpannerDbType.String},
            {"endTitle", SpannerDbType.String} });
    cmd.Parameters["startTitle"].Value = startTitle;
    cmd.Parameters["endTitle"].Value = endTitle;
    using (var reader = await cmd.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine("AlbumId : "
            + reader.GetFieldValue<string>("AlbumId")
            + " AlbumTitle : "
            + reader.GetFieldValue<string>("AlbumTitle")
            + " MarketingBudget : "
            + reader.GetFieldValue<string>("MarketingBudget"));
        }
    }
}

Go

func readStoringIndex(ctx context.Context, w io.Writer, client *spanner.Client) error {
	iter := client.Single().ReadUsingIndex(ctx, "Albums", "AlbumsByAlbumTitle2", spanner.AllKeys(),
		[]string{"AlbumId", "AlbumTitle", "MarketingBudget"})
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var albumID int64
		var marketingBudget spanner.NullInt64
		var albumTitle string
		if err := row.Columns(&albumID, &albumTitle, &marketingBudget); err != nil {
			return err
		}
		budget := "NULL"
		if marketingBudget.Valid {
			budget = strconv.FormatInt(marketingBudget.Int64, 10)
		}
		fmt.Fprintf(w, "%d %s %s\n", albumID, albumTitle, budget)
	}
}

Java

static void readStoringIndex(DatabaseClient dbClient) {
  // We can read MarketingBudget also from the index since it stores a copy of MarketingBudget.
  ResultSet resultSet =
      dbClient
          .singleUse()
          .readUsingIndex(
              "Albums",
              "AlbumsByAlbumTitle2",
              KeySet.all(),
              Arrays.asList("AlbumId", "AlbumTitle", "MarketingBudget"));
  while (resultSet.next()) {
    System.out.printf(
        "%d %s %s\n",
        resultSet.getLong(0),
        resultSet.getString(1),
        resultSet.isNull("MarketingBudget") ? "NULL" : resultSet.getLong("MarketingBudget"));
  }
}

Node.js

// "Storing" indexes store copies of the columns they index
// This speeds up queries, but takes more space compared to normal indexes
// See the link below for more information:
// https://cloud.google.com/spanner/docs/secondary-indexes#storing_clause

// Imports the Google Cloud client library
const Spanner = require('@google-cloud/spanner');

// Instantiates a client
const spanner = Spanner();

// Uncomment these lines to specify the instance and database to use
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const albumsTable = database.table('Albums');

const query = {
  columns: ['AlbumId', 'AlbumTitle', 'MarketingBudget'],
  keySet: {
    all: true
  },
  index: 'AlbumsByAlbumTitle2'
};

// Reads the Albums table using a storing index
albumsTable.read(query)
  .then((results) => {
    const rows = results[0];

    rows.forEach((row) => {
      const json = row.toJSON();
      console.log(`AlbumId: ${json.AlbumId.value}, AlbumTitle: ${json.AlbumTitle}, MarketingBudget: ${json.MarketingBudget.value}`);
    });
  });

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Reads sample data from the database using an index with a storing
 * clause.
 *
 * The index must exist before running this sample. You can add the index
 * by running the `add_storing_index` sample or by running this DDL statement
 * against your database:
 *
 *     CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle)
 *     STORING (MarketingBudget)
 *
 * Example:
 * ```
 * read_data_with_storing_index($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function read_data_with_storing_index($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $keySet = $spanner->keySet(['all' => true]);
    $results = $database->read(
        'Albums',
        $keySet,
        ['AlbumId', 'AlbumTitle', 'MarketingBudget'],
        ['index' => 'AlbumsByAlbumTitle2']
    );

    foreach ($results->rows() as $row) {
        printf('AlbumId: %s, AlbumTitle: %s, MarketingBudget: %d' . PHP_EOL,
            $row['AlbumId'], $row['AlbumTitle'], $row['MarketingBudget']);
    }
}

Python

def read_data_with_storing_index(instance_id, database_id):
    """Reads sample data from the database using an index with a storing
    clause.

    The index must exist before running this sample. You can add the index
    by running the `add_soring_index` sample or by running this DDL statement
    against your database:

        CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle)
        STORING (MarketingBudget)

    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    with database.snapshot() as snapshot:
        keyset = spanner.KeySet(all_=True)
        results = snapshot.read(
            table='Albums',
            columns=('AlbumId', 'AlbumTitle', 'MarketingBudget'),
            keyset=keyset,
            index='AlbumsByAlbumTitle2')

        for row in results:
            print(
                u'AlbumId: {}, AlbumTitle: {}, '
                'MarketingBudget: {}'.format(*row))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

result = client.read "Albums", [:AlbumId, :AlbumTitle, :MarketingBudget],
                     index: "AlbumsByAlbumTitle2"

result.rows.each do |row|
  puts "#{row[:AlbumId]} #{row[:AlbumTitle]} #{row[:MarketingBudget]}"
end

Indexing of NULLs

By default Cloud Spanner indexes NULL values. For example, recall the definition of the index SingersByFirstLastName on the table Singers:

CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName)

All rows of Singers are indexed even if either FirstName or LastName, or both, are NULL.

columns with NULL values are indexed

If an index contains NULLs, you can perform efficient SQL queries and reads that use NULLs. For example, use this SQL query statement to find all Singers with NULL FirstName:

SELECT s.SingerId, s.FirstName, s.LastName
FROM Singers@{FORCE_INDEX=SingersByFirstLastName} AS s
WHERE s.FirstName IS NULL

Sort order for NULLs

Cloud Spanner sorts NULLs as the smallest value for any given type. For a column in the ASC order, NULL values sort first, and for a column in the DESC order, NULL values sort last.

Disabling the indexing of NULLs

To disable the indexing of nulls, add the NULL_FILTERED keyword to the index definition. NULL_FILTERED indexes are particularly useful for indexing sparse columns, where most rows contain a NULL value. In these cases, the NULL_FILTERED index can be considerably smaller and cheaper to maintain than a normal index that includes NULLs.

Here's an alternate definition of SingersByFirstLastName that does not index NULLs:

CREATE NULL_FILTERED INDEX SingersByFirstLastNameNoNulls ON Singers(FirstName, LastName)

Note that the NULL_FILTERED keyword applies to all index key columns values. You cannot specify NULL-filtering on a per-column basis.

Making an index NULL_FILTERED may make it unusable for certain queries. For example, this query is invalid because it is missing a required NULL filtering expression:

FROM Singers@{FORCE_INDEX=SingersByFirstLastNameNoNulls}
WHERE FirstName = "John"

This query cannot actually use the SingersByFirstLastNameNoNulls index, because the index does not contain any Singers rows for which LastName is NULL.

To use the index successfully, you must rewrite the query:

SELECT FirstName, LastName
FROM Singers@{FORCE_INDEX=SingersByFirstLastNameNoNulls}
WHERE FirstName = "John" AND LastName IS NOT NULL

Unique indexes

Indexes can be declared UNIQUE. UNIQUE indexes add a constraint to the data being indexed that prohibits duplicate entries for a given index key. This constraint is enforced by Cloud Spanner at transaction commit time. Specifically, any transaction that would cause multiple index entries for the same key to exist will fail to commit.

If a table contains non-UNIQUE data in it to begin with, attempting to create a UNIQUE index on it will fail.

A note about UNIQUE NULL_FILTERED indexes

A UNIQUE NULL_FILTERED index does not enforce index key uniqueness when at least one of the index's key parts is NULL.

For example if you created the following table and index:

CREATE TABLE ExampleTable (
  Key1 INT64 NOT NULL,
  Key2 INT64,
  Key3 INT64,
  Col1 INT64,
) PRIMARY KEY (Key1, Key2, Key3);

CREATE UNIQUE NULL_FILTERED INDEX ExampleIndex ON ExampleTable (Key1, Key2, Col1);

The following two rows in ExampleTable have the same values for the secondary index keys Key1, Key2 and Col1:

1, NULL, 1, 1
1, NULL, 2, 1

Because Key2 is NULL and the index is NULL_FILTERED, the rows will not be present in the index ExampleIndex. Because they are not inserted into the index, the index will not reject them for violating uniqueness on (Key1, Key2, Col1).

If you want the index to enforce the uniqueness of values of the tuple (Key1, Key2, Col1), then you must annotate Key2 with NOT NULL in the table definition or create the index without NULL_FILTERED.

Adding an index

Cloud Spanner allows clients to add a new secondary index to an existing Cloud Spanner table while the database continues to serve traffic. Like any other schema changes in Cloud Spanner, adding an index to an existing database does not require taking the database offline and does not lock entire columns or tables.

Whenever a new index is added to an existing table, Cloud Spanner automatically backfills (i.e. populates) the index to reflect the up-to-date view of the data being indexed. Cloud Spanner manages this backfill process for you. Note that Cloud Spanner uses additional resources during the index backfill.

Dropping an index

Use the DROP INDEX statement to drop a secondary index from your schema.

To drop the index named SingersByFirstLastName:

DROP INDEX SingersByFirstLastName

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.

Send feedback about...

Cloud Spanner Documentation