MySQL Forums
Forum List  »  UDF

I need an alternative to make an index work
Posted by: Adnan Raza
Date: September 12, 2012 10:39PM

Hello,

I have applied indices(indexes) on two fields of a table which do work in normal cases but when I use ifnull method they don't work. I need an alternative to make index work by keeping the logic required in ifnull intact.

Please see,

CREATE TEMPORARY TABLE `temp_table` (
`id` int(11) NOT NULL auto_increment,
`dt1` datetime NOT NULL,
`dt2` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `i1` (`dt1`),
KEY `i2` (`dt2`)
) ENGINE=InnoDB;
insert into temp_table(dt1,dt2)
values
('2012-01-01','2011-01-01'),
('2012-02-01','2011-02-01'),
('2012-03-01','2011-03-01'),
('2012-04-01','2011-04-01'),
('2012-05-01','2011-05-01'),
('2012-06-01','2011-06-01'),
('2012-07-01','2011-07-01');


Now, following query will show that i2 is working

explain select * from temp_table
where dt2 > '2012-01-01';

following query will show that i1 is working
explain select * from temp_table
where dt1 > '2012-01-01';

but, when I use ifnull which is requirement, it does not work, neither i1 not i2 indexes.

explain select * from temp_table
where ifnull(dt1,dt2) > '2012-01-01';

Options: ReplyQuote


Subject
Views
Written By
Posted
I need an alternative to make an index work
3302
September 12, 2012 10:39PM


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.