Can you index tables differently on Master and Slave (MySQL)

Question

Is it possible to set up different indexing on a read only slave, from on the master? Basically, this seems like it makes sense given the different requirements of the two systems, but I want to make sure it will work and not cause any problems.

Answer 1

I believe so. After replication is working, you can drop the indexes on the slave and create the indexes you want and that should do it. Since MySQL replicates statements and not data (at least by default), as long as the SQL necessary to insert or update or select from the table doesn't need to change, it shouldn't notice.

Now there are obviously downsides to this. If you make a unique key that isn't on the master, you could get data inserted on the master that can't be inserted on the slave. If an update is done that uses an index it may run fast on the master but cause a table scan on the slave (since you don't have whatever index was handy).

And if any DDL changes ever happen on the master (such as to alter an index) that will be passed to the slave and the new index will be created there as well, even though you don't want it to.

Answer 2

For sure. I do it all the time. Issues I've run into:

• Referencing indexes via FORCE/USE/IGNORE INDEX in SELECTS will error out
• Referencing indexes in ALTER statments on the master can break replication
• Adds another step to promoting a slave to be the master in case of emergency
• If you're using statement based replication (the norm), and you're playing around with UNIQUE indexes, any INSERT... ON DUPLICATE KEY, INSERT IGNORE or REPLACE statments will cause extreme data drifting / divergence
• Performance differences (both good and bad)