Preventing MySQL index corruption

Question

I'm having a recurring issue with a MySQL database where one index in particular has been getting corrupted. Originally I saw a corruption event about every 2-3 weeks and this week I've seen it happen twice. The corruption also only happens on one index and the data itself seems fine.

The composite index in question spans two BigInt foreign key ID Columns on a table with 5M rows. I'm using InnoDB for all tables and running MySQL 5.6.23 on Amazon RDS. I looked to see if I could find anything in mysql-error but haven't found any entries in it.

To verify that the index corrupted, I run the following EXPLAIN:

mysql> explain SELECT * FROM  student INNER JOIN school ON school.id = student.school_id WHERE student.student_status_id IN (3, 4, 7) ORDER BY student.id desc LIMIT 0, 25;

+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+---------------------------------+
| id | select_type | table      | type | possible_keys                                                   | key                       | key_len | ref                   | rows | Extra                           |
+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+---------------------------------+
|  1 | SIMPLE      | school     | ALL  | PRIMARY                                                         | NULL                      | NULL    | NULL                  | 3690 | Using temporary; Using filesort |
|  1 | SIMPLE      | student    | ref  | student_status_id_idx,school_id_idx,schoolStatusIndex_idx       | schoolStatusIndex_idx     | 8       | school.id             |  178 | Using index condition           |
+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+————————————————+

When it’s “fixed” the rows are roughly 48/1 instead of 3690/178

To fix it, it requires some combination of these 3 steps:

1. alter table student drop KEY schoolStatusIndex_idx; alter table student add key schoolStatusIndex_idx (school_id,student_status_id);
2. alter table school engine=innodb; (rebuilds all indexes)
3. alter table student engine=innodb;

Still, this has me baffled. What could be causing this kind of index corruption and how can I prevent it from happening moving forward?

Here's the trimmed output of SHOW CREATE TABLE for student, student_status, and school:

CREATE TABLE `student_status` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;


CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `school_id` bigint(20) NOT NULL,
  `phone` varchar(10) NOT NULL,
  `student_status_id` bigint(20) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `student_status_id_idx` (`student_status_id`),
  KEY `school_id_idx` (`school_id`),
  KEY `schoolStatusIndex_idx` (`school_id`,`student_status_id`),
  CONSTRAINT `student_student_status_id_student_status_id` FOREIGN KEY (`student_status_id`) REFERENCES `student_status` (`id`),
  CONSTRAINT `student_school_id_school_id` FOREIGN KEY (`school_id`) REFERENCES `school` (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=2582686 DEFAULT CHARSET=latin1;


CREATE TABLE `school` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=4350 DEFAULT CHARSET=latin1;

And some additional output that might be helpful:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 5753536512 |
+-------------------------+------------+

Answer 1

I suspect that the MySQL query optimizer occasionally choose a sub-optimal path. I'm not certain why it would sometimes use the index and sometimes not, nor why it seemed related to the index in question.

I think the query itself could not be fully covered by the index (it included an ORDER BY that I did not cover). I made a change to the query to ORDER BY student.created_at and added an index on (created_at, school_id, student_status_id) and everything looks good now.