Skip to content

MySQL, Keys and Functions

I’m at the UTOSC, and I just learned something I want to remember:

The MySQL optimizer will not use keys if they are in a function. For example:

... where col_w_key + 1 = 3

would result in a full table scan. It should be rewritten, like so:

... where col_w_key = (3 - 1)

The basic idea is to rewrite:

... where func(key1) = val1

to be:

... where key1 = rev_func(val1)

I think I knew this implicitly all along, I never liked performing functions on performing functions on columns, but it’s good to know this explicitly.

Posted in MySQL, Tips and Tricks, utosc.

0 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

Some HTML is OK

or, reply to this post via trackback.