MySQL Forums
Forum List  »  Performance

Update table slower in trigger than in procedure. Is this possible?
Posted by: Emilio Nicolás
Date: October 27, 2009 07:11PM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
Update table slower in trigger than in procedure. Is this possible?
3268
October 27, 2009 07:11PM


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.