0

I am using to trigger on a table, after insert and delete, for counting items.

but i think it make query some inefficient.

a transaction inserting 250,000 rows, If trigger is on then it takes 75 seconds. but If trigger isn't then it takes 60 seconds.

when ever i saw some session variable turning off unique check... like that, Is there any way to turn off trigger?

I think MariaDB doesn't optimize trigger by anyway. ( I means it just repeat +1 operation 250,000.. not just +250,000)

below is my trigger.

CREATE TRIGGER incrementTableA
AFTER INSERT ON TableA
FOR EACH ROW
    UPDATE Counts 
    SET Counts.value = Counts.value + 1
    WHERE Counts.var='totalTableA';

CREATE TRIGGER decrementTotalTableA
AFTER DELETE ON TableA
FOR EACH ROW
    UPDATE Counts
    SET Counts.value = Counts.value - 1
    WHERE Counts.var='totalTableA';
JaeIL Ryu
  • 159
  • 10
  • 25% extra time is not bad when you are updating a row in addition to a normal insert. – The Impaler Jul 21 '21 at 01:17
  • Let's tackle a related issue -- What causes 250K rows to be inserted all at once? Let's see if we can speed that up, _then_ get back to bumping the counters. – Rick James Jul 21 '21 at 02:59
  • Do some of these posts answer your question? https://stackoverflow.com/questions/37131379/sql-disabling-trigger or https://stackoverflow.com/questions/13598155/how-to-disable-triggers-in-mysql – Roberto C. Rodriguez-Hidalgo Jul 21 '21 at 04:10
  • @RobertoC.Rodriguez-Hidalgo yes. i think Frist Answer is correct. but it seems very complex.... I understand the idea(session or user trigger, maybe?). but it is not Ideal.... Xd. – JaeIL Ryu Jul 21 '21 at 06:16

0 Answers0