Write latency for adding an index on read replica

Question

This is a question that I've not been able to find any good documentation for on the internet.

I think it's a common industry standard that for tables with heavy writes, you add an index on the read replica and not on the master. This way, you're ensuring the writes on master don't slow down, while also ensuring the reads on read replica are faster.

But doesn't the read replica also have writes, in the form of binlog replication? If I'm not wrong, the replication is simply a form of copy-pasting the commands from the binlog as-is. But the index on the read replica would still have to be updated, right? Since the binlog file won't have any "index updates" commands (because the master doesn't even have the index), I'm assuming that the "index update" on the read replica happens when an INSERT or an UPDATE commands comes from the binlog. So, is it still not a "WRITE" operation that happens? Will it still not slow down the write latency?

I understand that not all DBs have this capability of indexing a read replica separately, so this question is restricted to only those DBs that do, such as MySQL.

Answer 1

• Since all writes occur on both the Primary and the Replica, it is unclear whether you will be saving anything.
• The Replica should normally be "readonly", but you should be able to turn it off to add an index.
• Index updates are not sent in the binlog, even when the Primary has indexes.
• Replication is implanted in one of two ways: STATEMENT, in which each INSERT is re-parsed and run on the Repicas, or, ROW, in which the row is sent without needing to re-parse the statement. [I don't know for a fact that the index will be updated in the ROW case.] (MIXED is a 3rd option, but it has some types of statements being Statement versus Row.)