The following behavior occurs in MySQL 4.0.20 with both InnoDB and MyISAM tables. It happens on the prmary-key index as well as other indexed columns. The behavior does NOT occur in 3.23.56 and 3.23.58.
This is just setup... mysql> select @x := 1; mysql> explain SELECT @x := IF(MAX(id) > @x, MAX(id), @x) FROM user2; +-------+-------+---------------+---------+---------+------+-------+---- ---------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+-------+---------------+---------+---------+------+-------+---- ---------+ | user2 | index | NULL | PRIMARY | 4 | NULL | 10000 | Using index | +-------+-------+---------------+---------+---------+------+-------+---- ---------+ 1 row in set (0.07 sec) Here it thinks it's using the PRIMARY index, but since the table only has 10,000 rows it clearly isn't. (Query performance bears out that it is indeed scanning every row in the table) mysql> explain SELECT @y := IF(MAX(id) > @x, MAX(id), @x) FROM user2; +------------------------------+ | Comment | +------------------------------+ | Select tables optimized away | +------------------------------+ 1 row in set (0.00 sec) Changing the lvalue to something other than @x makes the problem go away. The query is fast again. mysql> explain SELECT IF(MAX(id) > @x, MAX(id), @x) FROM user2; +------------------------------+ | Comment | +------------------------------+ | Select tables optimized away | +------------------------------+ 1 row in set (0.10 sec) Same if there's no assignment at all. Is this a bug, or am I just misunderstanding a change in semantics? -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
