0

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$$
  • 1) (column = NEW.column or (column is Null and NEW.column is Null)) may be replaced with simple column <=> NEW.column. 2) Trigger fires for each separate row update, and it performs a selection (with plan building). Why you're surprised? 3) Create generated column which is a hash of concatenated row and check this hash interference. If hash is long enough then the collision probability will be close to zero. – Akina Oct 19 at 18:11
  • I cannot create such concatenated row because of the Nulls. Does <=> work with nulls? – relima Oct 19 at 18:18
  • have you tried it with a UNIQUE constraint for those columns except id?, i rhink itt ciuld be faster than a hash. – nbk Oct 19 at 19:34
  • Add 1 index on those columns combinations which is included into your where clause. – Shiwangini Shishulkar Oct 19 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.