This page describes how to add and drop indexes on Cloud SQL readreplicas. While a replica is normally read-only, there might be times you wantto createsecondary indexeson tables for reporting purposes. Cloud SQL offers a set ofstoredprocedures for managing these indexes.

Terminology

• Clustered index. The main index on a MySQL table that physically ordersthe rows on disk. When you define a primary key on a table, MySQL uses it asthe clustered index. There can only be one clustered index on a table.
• Secondary index. An additional index on a MySQL table that optimizesquery performance.

Stored procedures for indexes

Cloud SQL includes two stored procedures in themysql schema that youcan use to add and drop secondary indexes on a MySQL read replica. Note thatwhile these procedures can run on a primary source instance, they are designedfor read replicas.

mysql.addSecondaryIdxOnReplica

Adds a secondary index on the database. This stored procedure is a wrapper for theCREATE INDEX DDL statement.

Parameters:

• idxType - Type of index to create. For example, pass UNIQUE to create a unique index.
• idxName - Name of the index.
• tableName - Name of the table in the format of schema.name.
• idxDefinition - Definition of the index. Do not include outer parentheses.
• idxOption - Any additional options to pass on index creation. For example, in MySQL 8.0, an option could pass INVISIBLE for an invisible index.

Syntax:

mysql.addSecondaryIdxOnReplica(idxType, idxName, tableName, idxDefinition, idxOption)

mysql.dropSecondaryIdxOnReplica

Drops a secondary index on the database. This stored procedure is a wrapper for theDROP INDEX DDL statement.

Parameters:

• idxName - Name of the index.
• tableName - Name of the table in the format of schema.name.
• idxOption - Any additional options to pass when dropping an index. For example, an algorithm option like INPLACE.

Syntax:

mysql.dropSecondaryIdxOnReplica(idxName, tableName, idxOption)

For theidxType andidxOption parameters, consult the documentation for themajor version of MySQL running on the Cloud SQL instance.

Examples

Correct Usage

Here are some example invocations of the procedures. Suppose we have a tablewith the following definition.

CREATETABLEsampletest.t1(idint(10)unsignedNOTNULLAUTO_INCREMENT,first_namevarchar(64)NOTNULL,last_namevarchar(64)NOTNULL,license_idintNOTNULL,PRIMARYKEY(id),KEYidx_fname(first_name))ENGINE=InnoDBDEFAULTCHARSET=utf8

If you wanted to create a regular index namedt1_fname_lname on thefirst_name andlast_name columns, you would execute the following:

callmysql.addSecondaryIdxOnReplica('','t1_fname_lname','sampletest.t1','first_name, last_name','')

If you also wanted to create a unique index namedt1_license_id on thelicense_id column with the comment "unique license id," you would execute thefollowing:

callmysql.addSecondaryIdxOnReplica('unique','t1_license_id','sampletest.t1','license_id','comment \"unique license id\"')

If you then wanted to drop the t1_fname_lname index, you would execute the following:

callmysql.dropSecondaryIdxOnReplica('t1_fname_lname','sampletest.t1','')

Incorrect Usage

The following attempt to create an index on thefirst_name andlast_namecolumns fails due to the outer parentheses in the idxDefinition parameter.

callmysql.addSecondaryIdxOnReplica('','t1_extra_parenthesis','sampletest.t1','(first_name, last_name)','')

You can only add indexes on customer created tables. The following attempt tocreate an index on the host column of the mysql.servers table fails.

callmysql.addSecondaryIdxOnReplica('','idx_invalid','mysql.servers','host','')

You can only use thedropSecondaryIdxOnReplica procedure to drop indexespreviously created using theaddSecondaryIdxOnReplica procedure. For example,the following call to drop the existingidx_fname index fails.

callmysql.dropSecondaryIdxOnReplica('idx_fname','sampletest.t1','')

SQL injection in these procedure calls will fail. For example, the following SQLinjection with a comment sequence will fail.

callmysql.addSecondaryIdxOnReplica(\"user 'a'@'%' --\",'idx_fname','sampletest.t1','first_name','')

Similarly, this SQL injection attempt with a delimiter fails.

callmysql.addSecondaryIdxOnReplica('','idx_fname','sampletest.t1','first_name',';flush status')

Recreation of read replicas

Occasionally, when there's an issue, Cloud SQL recreates a read replicafrom the primary source in order to quickly recover the instance. Indexescreated on the read replica prior to a recreate operation are not persisted. Itis the responsibility of the customer to recreate these indexes using the storedprocedures on the read replica.

What's next

• Connecting using a database client from a local machine or Compute Engine