1

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
| improve this question | |
  • 1
    what is your table ENGINE? how many columns and which columns are indexed? – Mohammad Ali Akbari Jun 20 '12 at 14:07
  • 1
    Do you have cascade delete turned on? You could be deleting millions of records by deleting one in the parent table. Are there triggers on the table? Sometimes those affect delete speed as well. – HLGEM Jun 20 '12 at 14:26
  • 1
    Do you delete just 1 row? How long does it take? What's the size of one row (or of the whole table)? – ypercubeᵀᴹ Jun 20 '12 at 14:33
  • 1
    Please post the SHOW CREATE TABLE tblname\G in the Question – RolandoMySQLDBA Jun 20 '12 at 14:47
  • 4
    This may seem silly: Please show the DELETE query – RolandoMySQLDBA Jun 20 '12 at 14:53
2

Looking at your current information, I see you posted the following info from SHOW INDEXES FROM:

  • Key_name
  • Seq_in_index
  • Column_name
  • Collation
  • Cardinality

The indexes you have are

  • course_id
  • complete_flag
  • pass_flag
  • reported_flag
  • student_id_2
  • reported_status
  • course_id_index

Give the read/ratio of 70%/30%, running a DELETE against a Row in this InnoDB table is not as simple as one would think. Why?

InnoDB's Index structure and Locking. All secondary indexes in an InnoDB table have a key back to the Clustered Index (a.k.a. gen_clust_index). Therefore, index contention (intermittent deadlocking) would always be a problem. I have written past posts about this drawback

If you are always having this issue with DELETE on a single row, you may want to think about applying either a tombstone table or adding a deleted flag to your table. I wrote a post about this as well: Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios

| improve this answer | |

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.