MySQL – How to Improve Delete Performance

MySQLperformance

When I delete 5000 rows from a table .It is costs more than 2 seconds but if I change delete to select it is just costs a few milliseconds.

How can I improve the performance?

Here is the details:

There are 2 million rows. And the meeting_number have index.

    mysql> show table status like 'zm_meeting_ext'\G;
    *************************** 1. row ***************************
               Name: zm_meeting_ext
             Engine: InnoDB
            Version: 10
         Row_format: Compact
               Rows: 2018854
     Avg_row_length: 318
        Data_length: 643842048
    Max_data_length: 0
       Index_length: 1225310208
          Data_free: 59768832
     Auto_increment: NULL
        Create_time: 2015-08-29 05:48:54
        Update_time: NULL
         Check_time: NULL
          Collation: utf8_general_ci
           Checksum: NULL
     Create_options: 
            Comment: 
    1 row in set (0.24 sec)
mysql> show index  from zm_meeting_ext\G;
*************************** 1. row ***************************
        Table: zm_meeting_ext
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 2018856
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: zm_meeting_ext
   Non_unique: 0
     Key_name: meeting_ext_key
 Seq_in_index: 1
  Column_name: meeting_number
    Collation: A
  Cardinality: 2018856
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment:

delete Statement：

DELETE  FROM zm_meeting_ext 
        WHERE   meeting_number IN
                 (  
                    888234641
                 , 
                    713842289
                 , 
                    587345701
                 , 
                    543533869
                 , 
                    885497679
                 , 
                    805900814
                 , 
                    804899468
                 , 
                    115617318
                 , 
                    582279870
                .......
                5000 numbers
                )

So how can I improve the performance and what is the principle of the delete operation?

Best Answer

When You SELECT data - You are work with InnoDB memory cache, You not write to disk, You are not log operations.

It is normal when same select thousands time faster then DELETE.

When You run DELETE command, MySQL must:

  • do the same as SELECT - found rows
  • log operation (write into redo-log files)
  • mark rows as deleted
  • lock deleted rows - wait if any other operations use them
  • update indexes

2 sec it is not significant huge time, but if it make collisions for other operations - You can to split operation for chunks

Related Question