What would make a query that deletes a row using the primary key of the table .. take too long ?
This is a table with about 1.4M rows that gets .. 70%/30% read/write .
I don't know where to look.
- Engine: InnoDB
- Columns: 30 - 40
SHOW INDEXES FROM produced this:
course_id 1 course_id A 4516 complete_flag 1 complete_flag A 16 pass_flag 1 pass_flag A 16 reported_flag 1 reported_flag A 16 student_id_2 1 student_id A 50436 student_id_2 2 course_id A 907849 student_id_2 3 complete_flag A 907849 student_id_2 4 pass_flag A 907849 reported_status 1 reported_status A 16 reported_status 2 reported_reason A 16 course_id_index 1 course_id A 391 reported_flag 1 reported_flag A 16
OK, so eventually things started getting worse and worse and after tuning some other queries decided to restart the mysql server.
After restart the system needed to do a FSCK and after that it currently seems stable.
Thanks for the links in the answer, those will surely be very helpful in continuing to tune-up the database.
PS: the delete statement was a simple
delete from x_table where id = 1