MySQL DELETE takes many (40) seconds for few records while a SELECT of the same data is fast

Question

Can any one give some insight on why DELETE statement taking more time to execute? We have not created any TRIGGER or CASCADE. We started to notice this issue when migrated to 8.0 from 5.5. Is it a issue related to 8.0 parameter tuning?

DELETE s FROM storefront s LEFT JOIN MASTER m  ON m.userid=s.userid WHERE m.userid IS NULL

9 row(s) affected 
Execution Time : 40.816 sec
Transfer Time  : 0.001 sec
Total Time     : 40.817 sec

At the same time, the SELECT statement takes a few mill seconds only.

SELECT s.userid FROM storefront s LEFT JOIN MASTER m  ON m.userid=s.userid WHERE m.userid IS NULL

Total Time     : 0.05 sec

Any help would be appreciated. We have a better server hardware with 16 GB of RAM.

Here is the PROFILING info for DELETE statement.

It is CPU_user(19.046875 seconds) and CPU_system(43.156250 seconds) taking most of the time out of the total execution time 50.24 seconds ....

Answer 1

The keyword is "MyISAM". There are multiple reasons why it might be taking a long time.

(I assume both tables have an index on userid? And the column definition is the same?)

• SELECT will be very fast because it will just use the index to find the userids in question. 0.05 seconds almost seems "too slow".
• MyISAM will do a table lock for the duration of the DELETE.
• MyISAM stores each row somewhere in the .MYD file. The index is in the .MYI file, even for the Primary Key.
• I think a row is deleted by changing the initial byte of the row. (Actually a byte whose sole purpose is to indicate deleted.
• In MyISAM, normally, a row is a contiguous stream of bytes in the .MYD. Deletes and some Updates will leave holes in the .MYD.
• Inserts prefer to fill in the holes first, growing the file if that fails. Note that a large row will be a linked list of pieces. This leads, potentially, to slower and slower full-row Selects.
• Oracle plans to get rid of MyISAM.

Those do not really add up to an explanation for 40 seconds to delete only 9 rows. Please provide SHOW CREATE TABLE and EXPLAIN DELETE ...; there may be details you left out or that I am falsely assuming.

Bottom Line: Change to InnoDB, and these problems will go away.

Answer 2

This is not the Answer, but it can help to find where so much time is spend. Use PROFILING

SET PROFILING=ON;

Manual

**sample**

mysql> SET PROFILING=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from photons where photons >= 100;
+----+---------------------+---------+
| id | mytime              | photons |
+----+---------------------+---------+
|  1 | 2020-02-26 12:00:00 |     100 |
|  3 | 2020-02-26 12:01:00 |     100 |
|  4 | 2020-02-26 12:05:00 |     200 |
+----+---------------------+---------+
3 rows in set (0.01 sec)

mysql> SHOW PROFILE ALL;
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| Status               | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function       | Source_file          | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| starting             | 0.000130 | 0.000071 |   0.000057 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | NULL                  | NULL                 |        NULL |
| checking permissions | 0.002022 | 0.000127 |   0.000103 |                 2 |                   0 |          160 |             0 |             0 |                 0 |                 2 |                 1 |     0 | check_access          | sql_authorization.cc |         802 |
| Opening tables       | 0.000474 | 0.000040 |   0.000033 |                 1 |                   0 |            8 |             0 |             0 |                 0 |                 1 |                 1 |     0 | open_tables           | sql_base.cc          |        5715 |
| init                 | 0.000043 | 0.000023 |   0.000020 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | handle_query          | sql_select.cc        |         121 |
| System lock          | 0.000811 | 0.000046 |   0.000035 |                 2 |                   0 |           16 |             0 |             0 |                 0 |                 2 |                 2 |     0 | mysql_lock_tables     | lock.cc              |         323 |
| optimizing           | 0.000474 | 0.000027 |   0.000023 |                 1 |                   0 |            8 |             0 |             0 |                 0 |                 1 |                 1 |     0 | optimize              | sql_optimizer.cc     |         151 |
| statistics           | 0.000027 | 0.000014 |   0.000012 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         367 |
| preparing            | 0.000763 | 0.000044 |   0.000036 |                 2 |                   0 |           16 |             0 |             0 |                 0 |                 2 |                 2 |     0 | optimize              | sql_optimizer.cc     |         475 |
| executing            | 0.000007 | 0.000003 |   0.000003 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                  | sql_executor.cc      |         119 |
| Sending data         | 0.000103 | 0.000057 |   0.000046 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                  | sql_executor.cc      |         195 |
| end                  | 0.001408 | 0.000062 |   0.000050 |                 1 |                   0 |          248 |             0 |             0 |                 0 |                 1 |                 0 |     0 | handle_query          | sql_select.cc        |         199 |
| query end            | 0.000378 | 0.000029 |   0.000022 |                 1 |                   0 |            8 |             0 |             0 |                 0 |                 1 |                 1 |     0 | mysql_execute_command | sql_parse.cc         |        4946 |
| closing tables       | 0.000409 | 0.000022 |   0.000020 |                 1 |                   0 |            8 |             0 |             0 |                 0 |                 1 |                 1 |     0 | mysql_execute_command | sql_parse.cc         |        4998 |
| freeing items        | 0.000030 | 0.000017 |   0.000012 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_parse           | sql_parse.cc         |        5610 |
| cleaning up          | 0.000026 | 0.000014 |   0.000012 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | dispatch_command      | sql_parse.cc         |        1924 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
15 rows in set, 1 warning (0.00 sec)

mysql>