Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
10.2.11
-
None
Description
A slow query log entry for a DELETE statement showed the following:
# User@Host: user[user] @ test.domain.com [10.6.184.141]
|
# Thread_id: 75816 Schema: db QC_hit: No
|
# Query_time: 420.906319 Lock_time: 0.086885 Rows_sent: 0 Rows_examined: 9734191
|
# Rows_affected: 0
|
#
|
# explain: id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
|
# explain: 1 SIMPLE tab ALL a_timestamp_indx NULL NULL NULL 9330481 9734191.00 100.00 55.55 Using where
|
#
|
SET timestamp=1535157447;
|
delete from tab where a_timestamp < (unix_timestamp(now())-14400)*1000000;
|
We can see that Rows_affected is 0. Supposedly, r_filtered is 55.55%.
The documentation has the following definition of r_filtered:
r_filtered is an observation-based counterpart of the filtered column. It shows which fraction of rows was left after applying the WHERE condition.
https://mariadb.com/kb/en/library/analyze-statement/
If 55.55% of rows were left after applying the WHERE clause, then how were 0 rows affected by the delete? It seems likely that either r_filtered is completely wrong or the definition of the column is wrong.