MySQL Forums
Forum List  »  Optimizer & Parser

optimizing abs()
Posted by: Michiel Betel
Date: October 25, 2006 04:31AM

It looks like abs() on an indexed field first 'abs-es' all fields and then does the search... this should be the other way around!

explain select count(*) from dd.bankmutaties_derdengeld where abs(bedrag)=169.40\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: bankmutaties_derdengeld
type: index
possible_keys: NULL
key: idx_bedrag
key_len: 6
ref: NULL
rows: 984558
Extra: Using where; Using index
1 row in set (0.00 sec)

Using an OR only examines 5 rows!

explain select count(*) from dd.bankmutaties_derdengeld where bedrag=169.4 or bedrag=-169.4\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: bankmutaties_derdengeld
type: range
possible_keys: idx_bedrag
key: idx_bedrag
key_len: 6
ref: NULL
rows: 5
Extra: Using where; Using index
1 row in set (0.00 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
optimizing abs()
3195
October 25, 2006 04:31AM
2253
October 25, 2006 05:37AM
1968
October 29, 2006 07:31AM


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.