UDF is no longer optimal

[Chris Canipe]

We're running 5.0.20a and 5.1.36.

On 5.0.20a a SELECT statement using a UDF (user-defined function) is optimized as shown via EXPLAIN:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: book_history
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: book_history
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: 
         rows: 1
        Extra: 
2 rows in set (0.00 sec)

And using the exact same data set, query, and UDF on 5.1.36 shows a different story (it's slower):

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: book_history
         type: index
possible_keys: NULL
          key: press_division_id
      key_len: 2
          ref: NULL
         rows: 2333
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: book_history
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: 
2 rows in set (0.01 sec)

The query's form is "SELECT id FROM table WHERE id = UDF((SELECT another_id FROM table WHERE id = 12345))"

The function is "NOT DETERMINISTIC" (by default) in both versions. If I change it to "DETERMINISTIC" on 5.1.36 then the output from EXPLAIN matches that of 5.0.20a and it's much faster.

I've been digging through the change logs to figure out when, where, and why this change occurred, but I'm coming up empty-handed.

The 5.1 docs state "Prior to MySQL 5.1.21, the DETERMINISTIC characteristic is accepted, but not used by the optimizer." If this is so, why is 5.0.20a optimizing the query? I'm baffled.

Thanks for your help.



Edited 1 time(s). Last edit at 05/10/2010 02:12PM by Chris Canipe.