optimizing abs()
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)
Subject
Views
Written By
Posted
optimizing abs()
3268
October 25, 2006 04:31AM
2284
October 25, 2006 05:37AM
1991
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.