MySQL Forums :: Performance :: Update table slower in trigger than in procedure. Is this possible?

Hi all!

I have a problem and I don't know how to solve it, I wish you could help me. Thanks in advance!

I have developed a materialized view in MySql and it has about 1 million rows. When a table called 'users' is updated, the trigger AFTER UPDATE has to update as well the materialized view witch has relationship with the row UPDATE in 'users'. The trigger looks like follows:

CREATE TRIGGER `usersAfterUpdateTG` AFTER UPDATE ON `users`
FOR EACH ROW BEGIN
#mv: update rows
UPDATE mv SET
name = NEW.name,
lastname = NEW.lastname,
countryName = NEW.countryName
WHERE idFriend = OLD.id;
END

The problem is that the query UPDATE FROM users WHERE id = 1 spends a lot of time (around 10 seconds) and I cannot improve the performance.

* I have an index in the column mv(idFriend)
* The most extrange thing is that if I make this update in a direct way from the MySQL client, the query is direct and perfectly done in just a few milliseconds. The query would be like follows:
UPDATE mv SET
name = 'testName',
lastname = 'testLastName',
countryName = 'Spain'
WHERE idFriend = 1;
* Moreover, if I make a PROCEDURE with the above UPDATE, it also spends just milliseconds when I execute the CALL statement.

Is this a MySQL bug or deficience? Is this possible? Could you please help me in any way?

Thank you very much!

Regards!



Edited 1 time(s). Last edit at 10/28/2009 09:36AM by Emilio Nicolás.