Bug #81558 prune_log event doesn't use any index
Submitted: 24 May 2016 4:56 Modified: 24 May 2016 6:19
Reporter: Tsubasa Tanaka (OCA) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Fabric Severity:S2 (Serious)
Version:1.5.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: delete, Mysql Fabric, prune_error_log, prune_log

[24 May 2016 4:56] Tsubasa Tanaka
Description:
MySQL Fabric deletes `log` table by prune_log event-schedule.

```
mysql> SHOW CREATE EVENT prune_log\G
*************************** 1. row ***************************
               Event: prune_log
            sql_mode: NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`fabric`@`127.0.0.1` EVENT `prune_log` ON SCHEDULE EVERY 3600 SECOND STARTS '2016-04-04 15:49:10' ON COMPLETION NOT PRESERVE ENABLE DO DELETE FROM log WHERE TIMEDIFF(UTC_TIMESTAMP(), reported) > MAKETIME(3600,0,0)
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)
```

But its query doesn't use any index.

How to repeat:
```
mysql> EXPLAIN DELETE FROM log WHERE TIMEDIFF(UTC_TIMESTAMP(), reported) > MAKETIME(0,0,3600);
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | DELETE      | log   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 17796 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set (0.00 sec)
```

I changed MAKETIME's argument. See also Bug#81557

Suggested fix:
Fix the query in prune_log and prune_error_log like this.

```
mysql> EXPLAIN DELETE FROM log WHERE reported < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 3600 SECOND);
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
|  1 | DELETE      | log   | NULL       | range | key_reported  | key_reported | 7       | const | 2340 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)
```
[24 May 2016 6:19] MySQL Verification Team
Hello Tanaka-San,

Thank you for the report.

Thanks,
Umesh
[6 Jul 2017 19:20] Bugs System
Status updated to 'Won't fix' (Fabric is now covered under Oracle Lifetime Sustaining Support)