Update MySQL table any time another table changes

Question

I am trying to reduce the number of queries my application uses to build the dashboard and so am trying to gather all the info I will need in advance into one table. Most of the dashboard can be built in javascript using the JSON which will reduce server load doing tons of PHP foreach, which was resulting in excess queries.

With that in mind, I have a query that pulls together user information from 3 other tables, concatenates the results in JSON group by family. I need to update the JSON object any time anything changes in any of the 3 tables, but not sure what the "right " way to do this is.

I could set up a regular job to do an UPDATE statement where date is newer than the last update, but that would miss new records, and if I do inserts it misses updates. I could drop and rebuild the table, but it takes about 16 seconds to run the query as a whole, so that doesn't seem like the right answer.

Here is my initial query:

SET group_concat_max_len = 100000;
SELECT family_id, REPLACE(REPLACE(REPLACE(CONCAT("[", GROUP_CONCAT(family), "]"), "\\", ""), '"[', '['), ']"', ']') as family_members
FROM (

SELECT family_id,
    JSON_OBJECT(
"customer_id",                      c.id, 
"family_id",                        c.family_id,
"first_name",                       first_name,
"last_name",                        last_name,
"balance_0_30",                     pa.balance_0_30,
"balance_31_60",                    pa.balance_31_60,
"balance_61_90",                    pa.balance_61_90,
"balance_over_90",                  pa.balance_over_90,
"account_balance",                  pa.account_balance,
"lifetime_value",                   pa.lifetime_value,
"orders",                           CONCAT("[", past_orders, "]")
) AS family

FROM
    customers AS c
LEFT JOIN accounting AS pa ON c.id = pa.customer_id
LEFT JOIN (
    SELECT patient_id, 
        GROUP_CONCAT(
            JSON_OBJECT(
            "id",                   id,
            "item",                 item,
            "price",                price,
            "date_ordered",         date_ordered
            )
        ) as past_orders 
    FROM orders
    WHERE date_ordered < NOW()
    GROUP BY customer_id
) AS r ON r.customer_id = c.id
where c.user_id = 1

) AS results
GROUP BY family_id

I briefly looked into triggers, but what I was hoping for was something like:

create TRIGGER UPDATE_FROM_ORDERS
AFTER INSERT OR UPDATE
ON orders
(EXECUTE QUERY FROM ABOVE WHERE family_id = orders.family_id)

I was hoping to create something like that for each table, but at first glance it doesn't look like you can run complex queries such as that where we are creating nested JSON.

Am I wrong? Are triggers the right way to do this, or is there a better way?

Answer 1

As a demonstration:

DELIMITER $$

CREATE TRIGGER orders_au
ON orders
AFTER UPDATE
FOR EACH ROW
BEGIN
   SET group_concat_max_len = 100000
   ;
   UPDATE target_table t
      SET t.somecol = ( SELECT expr 
                          FROM ...
                         WHERE somecol = NEW.family_id
                         ORDER BY ...
                         LIMIT 1
                      )
    WHERE t.family_id = NEW.family_id
   ;
END$$

DELIMITER ;

---

Notes:

MySQL triggers are row level triggers; a trigger is fired for "for each row" that is affected by the triggering statement. MySQL does not support statement level triggers.

The reference to NEW.family_id is a reference to the value of the family_id column of the row that was just updated, the row that the trigger was fired for.

MySQL trigger prohibits the SQL statements in the trigger from modifying any rows in the orders table. But it can modify other tables.

SQL statements in a trigger body can be arbitrarily complex, as long as its not a bare SELECT returning a resultset, or DML INSERT/UPDATE/DELETE statements. DDL statements (most if not all) are disallowed in a MySQL trigger.