Adding index only on read replica of MySQL RDS instance

Question

I have created read replica of RDS production database for reporting purposes. Cross-team is using the replica for their use.

Consumers of this replica are looking for additional indexes for faster search, but the developers are not in favour of that (adding these indexes to master) concerning more time for the insertion of records in the master database.

Is it possible to add index only on read replica of MySQL database? How this is practised in the industry? Or what other ways this problem could be solved?

Answer 1

If by any chance you are using AWS MySQL RDS as your database infra, you can refer this:

To further maximize read performance, Amazon RDS for MySQL allows you to add table indexes directly to Read Replicas, without those indexes being present on the master.

• K D: Thanks, that link was useful. I tried doing this on testing environment. Definitely, this requires to allow modifications to read replica.
• Andrea Gorrieri: How can I allow modifications on the read replica? Modifications made on the read replica are copied automatically to the Master?
• K D: Amazon RDS console allows to configure replica settings to accept changes. This way replica is not a truly a read only database (accepting changes ONLY from master). Architects has to be very cautious for this kind of configuration but it is helpful in certain scenarios.
• billias: @KD how is what you describe achievable? Experimenting with the parameters group of a read replica, I observed that although the read_only parameter is modifiable, the innodb_read_only parameter (which is the one that actually needs to change in mysql in order to write to a database) is not modifiable.
• K D: By default innodb_read_only parameter of MySQL is set to 0 and thus you can make modification in the database. This question was around the restriction from AWS on read replica. Obviously, if you have set innodb_read_only=1 in the master database there will be no effect of changes in the replica settings in AWS console. Ref: dev.mysql.com/doc/refman/5.7/en/innodb-read-only-instance.html