Pass parameter to trigger dynamically

Question

I have a table on a mysql 5.7 db, containing say athletes with their mean, max, avg times in a specific sport.

I have another table that lists some calculated statistics based on those values.

I managed to do the calculcations that end up on the second using stored procedures.

I use as input parameter to the stored procedure the athlete's name.

So when in the first table, an athlete is inserted (with his/her avg/min/max times) or his/her values are updated and I run the stored procedure, the later updates the statistics table.

My question is how to achieve the same result with triggers.

I guess it is feasible/easy to update the entire table on each insert or update of the first table.

What would be more efficient performance-wise, would be on each

INSERT into table1 values (..) where athlete_name="John Do"
(...)
ON DUPLICATE KEY UPDATE (...)

run a trigger in the pseudocode form

INSERT into statistics_table values (..) where athlete_name="John Do"
ON DUPLICATE KEY UPDATE (...)

How can the the athlete_name="John Do" be passed to the trigger dynamically, to avoid update the entire statistics table?

• Akina: Read about pseudotable NEW available in a trigger.
• danblack: Have you got poor results in querying statistical data to resort to this? Or are you assuming this trigger/stored procedures overhead will get a better result?

Answer 1

Since recalculating rankings for a large table can take a long time, and doing that frequently can mean a really long time, start with this question:

1. Will the recalculating during SELECT be "fast enough"? If the answer to this question is 'yes', then simply do it.

2. Will the recalculation will be "fast enough" to do during INSERTs. Also, ask whether the burden on the system is "too much". If this is OK, then simply do it.

3. If both of those don't pass muster, periodically recompute the rankings.

Note that MySQL 8.0 and MariaDB 10.2 have ranking and other "windowing functions". However, you need to test them -- they may not be "fast enough"; after all 'ranking' probably requires reading and sorting the entire table.