MySQL Forums
Forum List  »  Performance

Slow query when where clause contains function
Posted by: Vince Sempronio
Date: December 12, 2012 05:45AM

in the following examples, myfun() is a DB function that modifies a string, it does not have any database accesses and col1 is indexed.

select * from tablea where col1 = myfun('constant');

is far different than

select * from tablea where myfun(col1) = 'constant';

or one would think.


the latter example obviously needs to examine each row in order to determine if the data from each row matches the constant, but in the former, fmfun('constant') is no different than comparing against the constant itself, but it turns out that the query behaves exactly the same way as the latter example.

is this an oversight on behalf of the SQL parser and it doesn't know to resolve the myfun(col1) BEFORE it applies the rest of the logic?

by the way, the same query

select * from tablea where col1 = 'constant';

returns a result "instantly".

am i missing something here?

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow query when where clause contains function
4647
December 12, 2012 05:45AM


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.