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

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.

Leave a Comment