Bug #42061 Different intervals for purging cause the shorter interval to full scan dc table

Description:
Using a long interval for the long data collection purging (such as 6 weeks), and a short interval for the query analysis purging (such as 1 week) causes the QUAN purge EXPLAIN for the INSERT ... SELECT in to the temp_dc_ng_*_now table to full scan the dc_ng_*_now.end_time index, such as the following SELECT:

 explain SELECT instance_attribute_id, end_time, end_time  FROM dc_ng_long_now JOIN inventory_instance_attributes USING  (instance_attribute_id) JOIN inventory_instances USING (instance_id) WHERE  dc_ng_long_now.end_time <= 1230814870074 AND  dc_ng_long_now.instance_attribute_id AND type_id in (8, 9, 7, 6) ORDER BY  dc_ng_long_now.end_time ASC LIMIT 10000\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dc_ng_long_now
         type: range
possible_keys: PRIMARY,end_time
          key: end_time
      key_len: 8
          ref: NULL
         rows: 8205369
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: inventory_instance_attributes
         type: eq_ref
possible_keys: PRIMARY,instance_id
          key: PRIMARY
      key_len: 4
          ref: mem.dc_ng_long_now.instance_attribute_id
         rows: 1
        Extra: 
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: inventory_instances
         type: eq_ref
possible_keys: PRIMARY,FKD4320F5BBDD9C29B
          key: PRIMARY
      key_len: 4
          ref: mem.inventory_instance_attributes.instance_id
         rows: 1
        Extra: Using where
3 rows in set (0.55 sec)

How to repeat:
On an instance with 7 weeks of data:

o Set the data collection purge to 6 weeks
o Set the query analysis purge to 1 week
o Check the run times of the queries with SHOW PROCESSLIST/SHOW INNODB STATUS for the MEM repository

Suggested fix:
Researching now.

in the trunk delete is now

DELETE FROM dc_foo WHERE end_time <= ? LIMIT ?

mysql> -- DELETE FROM dc_ng_string_now WHERE end_time <= 1236685908715 LIMIT 10000
mysql> EXPLAIN SELECT * FROM dc_ng_string_now WHERE end_time <= 1236685908715 LIMIT 10000;
+----+-------------+------------------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table            | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+------------------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | dc_ng_string_now | range | end_time      | end_time | 8       | NULL |    1 | Using where | 
+----+-------------+------------------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

Verified fixed on 2.1.0.1032

mysql> EXPLAIN SELECT * FROM dc_ng_string_now WHERE end_time <= 1241114637951  LIMIT 10000;
+----+-------------+------------------+-------+---------------+----------+---------+------+-----------+-------------+
| id | select_type | table            | type  | possible_keys | key      | key_len | ref  | rows      | Extra       |
+----+-------------+------------------+-------+---------------+----------+---------+------+-----------+-------------+
|  1 | SIMPLE      | dc_ng_string_now | range | end_time      | end_time | 8       | NULL | 239727583 | Using where | 
+----+-------------+------------------+-------+---------------+----------+---------+------+-----------+-------------+
1 row in set (0.00 sec)

(Tested with 8 weeks of data )

An entry was added to the 2.1.0 changelog:

Using a long interval for the long data collection purging (such as 6 weeks), and a short interval for the query analysis purging (such as 1 week) caused the Query Analyzer purge EXPLAIN for the INSERT ... SELECT into the temp_dc_ng_*_now table to perform a full scan on the dc_ng_*_now.end_time index. For example:

explain SELECT instance_attribute_id, end_time, end_time  FROM dc_ng_long_now JOIN
inventory_instance_attributes USING  (instance_attribute_id) JOIN inventory_instances
USING (instance_id) WHERE  dc_ng_long_now.end_time <= 1230814870074 AND 
dc_ng_long_now.instance_attribute_id AND type_id in (8, 9, 7, 6) ORDER BY 
dc_ng_long_now.end_time ASC LIMIT 10000\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dc_ng_long_now
         type: range
possible_keys: PRIMARY,end_time
          key: end_time
      key_len: 8
          ref: NULL
         rows: 8205369
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: inventory_instance_attributes
         type: eq_ref
possible_keys: PRIMARY,instance_id
          key: PRIMARY
      key_len: 4
          ref: mem.dc_ng_long_now.instance_attribute_id
         rows: 1
        Extra: 
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: inventory_instances
         type: eq_ref
possible_keys: PRIMARY,FKD4320F5BBDD9C29B
          key: PRIMARY
      key_len: 4
          ref: mem.inventory_instance_attributes.instance_id
         rows: 1
        Extra: Using where
3 rows in set (0.55 sec)