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.
Thanks.
EDIT
- Engine: InnoDB
- Columns: 30 - 40
- Indexes:
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
No Triggers
No Cascading
UPDATE
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
SHOW CREATE TABLE tblname\G
in the Question