sql_no_cache added by the optimizer

[alan langridge]

Hi,

Can anyone answer why the optimizer sometimes adds a "sql_no_cache" to a query, and what rules does it use to decide to do this?

An example of this is shown here:

mysql> explain extended select count(*) from db.tbl where dtm < date_sub(current_date(), interval @months month);
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
| 1 | SIMPLE | tbl | range | ixDtm | ixDtm | 9 | NULL | 5993 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------+
| Note | 1003 | select sql_no_cache count(0) AS `count(*)` from db.tbl where (db.tbl.dtm < 20070208000000) |
+-------+------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

This was done in MySQL 4.1.14 on an InnoDB table.

Thanks,
Alan