MySQL Forums
Forum List  »  General

DELETE showing on slow query log
Posted by: John Wythe
Date: December 21, 2017 09:05AM

I am running MYSQL 5.0.95 on Redhat Linux.
I have slow query log turned on and the option to log queries that do not use indexes.

I have a query "DELETE from inmemo_00000000 where `jn`='1111';

Here is my table definition.

+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| jn | bigint(20) unsigned | NO | PRI | 0 | |
| db | varchar(255) | NO | PRI | | |
| tbl | varchar(255) | NO | PRI | | |
| rn | int(10) unsigned | NO | PRI | 0 | |
| E | int(11) | NO | PRI | 0 | |
| lw | int(10) unsigned | NO | PRI | 0 | |
+-------+---------------------+------+-----+---------+-------+

+-----------------+------------+----------+--------------+-------------+--------
---+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collati
on | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+----------+--------------+-------------+--------
---+-------------+----------+--------+------+------------+---------+
| inmemo_00000000 | 0 | PRIMARY | 1 | jn | NULL
| NULL | NULL | NULL | | HASH | |
| inmemo_00000000 | 0 | PRIMARY | 2 | db | NULL
| NULL | NULL | NULL | | HASH | |
| inmemo_00000000 | 0 | PRIMARY | 3 | tbl | NULL
| NULL | NULL | NULL | | HASH | |
| inmemo_00000000 | 0 | PRIMARY | 4 | rn | NULL
| NULL | NULL | NULL | | HASH | |
| inmemo_00000000 | 0 | PRIMARY | 5 | E | NULL
| NULL | NULL | NULL | | HASH | |
| inmemo_00000000 | 0 | PRIMARY | 6 | lw | NULL
| 0 | NULL | NULL | | HASH | |
| inmemo_00000000 | 1 | db | 1 | db | NULL
| NULL | NULL | NULL | | HASH | |
| inmemo_00000000 | 1 | db | 2 | tbl | NULL
| NULL | NULL | NULL | | HASH | |
| inmemo_00000000 | 1 | db | 3 | rn | NULL
| NULL | NULL | NULL | | HASH | |
| inmemo_00000000 | 1 | db | 4 | E | NULL
| 0 | NULL | NULL | | HASH | |
| inmemo_00000000 | 1 | jn | 1 | jn | NULL
| 0 | NULL | NULL | | HASH | |
+-----------------+------------+----------+--------------+-------------+------

I do not understand why the slow query log shows this query when:
1) it should be using an index
2) the table is using the memory engine with few or no entries most of the time.
3) The entries do not show up if I turn on log queries that do not use indexes.
4) the query time is well over 15 secs which is the setting for what is to be considered a long query. As high as 95 secs.

Note: I added the index for jn after my testing. it should have been using the primary index, since the first field is jn.

My questions are why does it show in the slow query log only when show queries that don't use indexes is on, and why is it taking so long? Can I trust that MySQL is reporting things correctly?

Options: ReplyQuote


Subject
Written By
Posted
DELETE showing on slow query log
December 21, 2017 09:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.