I am using a trigger to check if a row exists. The code below works, but it is very very slow. I suspect that it has to do with the way I am handling NULLs.
Any suggestion to optimize it?
DELIMITER $$ CREATE TRIGGER Before_Update BEFORE UPDATE ON mytable FOR EACH ROW BEGIN IF (EXISTS(SELECT 1 FROM mytable WHERE (Area = NEW.Area) and (Variavel = NEW.Variavel) and (`Data` = NEW.`Data`) and (Safra = NEW.Safra or (Safra is Null and NEW.Safra is Null)) and (Futura = NEW.Futura or (Futura is Null and NEW.Futura is Null)) and (Fonte = NEW.Fonte or (Fonte is Null and NEW.Fonte is Null)) and (Localidade = NEW.Localidade or (Localidade is Null and NEW.Localidade is Null)) and id != NEW.id )) THEN SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'ERROR! ROW EXISTS'; END IF; END$$