MySQL Forums
Forum List  »  Optimizer & Parser

UDF is no longer optimal
Posted by: Chris Canipe
Date: May 10, 2010 02:07PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
UDF is no longer optimal
2323
May 10, 2010 02:07PM
1504
May 10, 2010 03:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.