4

I already have a my system with Master-Slave replication.

I have started using Slave for data fetching. So, I need to create some extra indexes on Slave.[Not In master - however be informed that the Master already has indexes].

I already read some posts but couldn't clear my doubts. They say If you create primary key-foreign key-unique index on slave then there could be an issue. however I'm not going to go for any of these constraints on slave.

I simply want to create non-clustered/secondary indexs on slave. would I face any issues if I do so ?

Thank you.

  • 1
    Adding a FOREIGN KEY on the Slave only could lead to a violation that was not caught on the Master. – Rick James Jun 29 '15 at 16:01
6

You can create secondary indexes on slave whenever required. There is no issue in creating secondary indexes (Non Primary and Non Unique).

If you create Primary and Unique on slave there will be chances that your replication will fail with duplicate key error on slave.

Take a example I have a table testRepl on master without having any index on it and has following structure and data

show create table testRepl\G
*************************** 1. row ***************************
       Table: testRepl
Create Table: CREATE TABLE `testRepl` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

SELECT * FROM testRepl;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Abdul |
|    2 | Jai   |
+------+-------+
2 rows in set (0.00 sec)

Initially we will have same table structure and data on slave as well , but assume you created a Primary key Index on id column on slave, and someone inserted id value as 2 on master on master the insertion would be successful but it will fail on slave as value 2 already exists there and the column is primary key indexed. So the insertion will fail and replication will break.

  • thats right, and Understood, So, this would be only issue right ???? I'm certainly not going to create primary or unique index on slave. I just want to create multiple column secondary indexes that's it. it won't have any issues right ?? – Hytool Jun 29 '15 at 11:52
  • Yes correct on slave you can create your indexes – Abdul Manaf Jun 29 '15 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.