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?
Subject
Written By
Posted
DELETE showing on slow query log
December 21, 2017 09:05AM
December 21, 2017 09:20AM
December 21, 2017 10:43AM
December 21, 2017 12:09PM
December 21, 2017 04:56PM
December 21, 2017 07:40PM
December 21, 2017 07:40PM
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.