0

Problem

Is there a way to automatically count related tables (one to Many), where the count will automatically increment / decrement depending if we add or remove a related item.

So, I could obviously just do a COUNT, but for perfomance reason it is expensive millions of records and are queries many many times. As I solution, rather then count every time, I would actually create a counter where add 1 when adding a new related item or remove 1 when deleting or de-ferencing another item

In order to do that, I may just create another table that serves as a counter, and query that table without counting.

Is there a better way, preferably, that is automatic?

Example

schema

create table object
(
    object_id       int auto_increment   primary key,
    name            varchar(120)         not null
);


create table item
(
    item_id       varchar(63)         not null,
    object_id int                     not null,
    primary key (object_id, item_id)
);
insert into object (name) VALUES ("hello");
insert into item (item_id, object_id) VALUES
      ("item1", 1),
      ("item2", 1),
      ("item3", 1),
      ("item4", 1);

Object "hello" has 4 items:

select count(*) from item where object_id = 1;
-- ouput: 4

However, I found that as a work around, I can create a counter (that is using back-end Python) that each time you do a CRUD operation, the counter is updated. For example:

Counter schema

create table item_counter
(
    counter bigint                  NOT NULL DEFAULT 0,
    object_id int                   NOT NULL  primary key
)

So now the ORM in the could we would do something like this (again, it will be handle in Python, but it doesn't matter is just as example:

-- create object AND item_counter
insert into object (name) VALUES ("hello");
-- create in the same time a counter 
insert into item_counter (object_id) VALUES ((SELECT object_id FROM object where name = "hello"));
-- create items
insert into item (item_id, object_id) VALUES  ("item1", 2);
update item_counter set counter = counter + 1 where object_id = 2;
insert into item (item_id, object_id) VALUES  ("item2", 2);
update item_counter set counter = counter + 1 where object_id = 2;
insert into item (item_id, object_id) VALUES  ("item3", 2);
update item_counter set counter = counter + 1 where object_id = 2;
insert into item (item_id, object_id) VALUES  ("item4", 2);
update item_counter set counter = counter + 1 where object_id = 2;
-- select the counter instead
select counter from item_counter where object_id = 2;

That if it was in python it would look like


# pseudo ORM code 
class ItemORM:

    def save(self, item_id, object_id):
        self.orm.save(item_id, object_id)
        counter = self.orm.get_counter(object_id)
        counter.add()


So, is there a better way, especially, something that MySQL may do automatically?

More Context

In case you want to know why, let's say that I am working in a large code base, which lots of legacy code and API dependecies used by external clients and currently there isn't a counter implementation but is merely my idea to overcome it. So, changing large part of the code is very risky, instead, tweak come MYSQL tables may be a better solution.

1
  • Use a triggers in mysql to update the new table, that fire on delete and insert. Commented Dec 27, 2022 at 18:55

1 Answer 1

2

The task can be easily solved with trigger(s).

For example, if INSERT is the only action on item table then only AFTER INSERT trigger needed:

CREATE TRIGGER tr_ai_update_item_count
AFTER INSERT ON item
FOR EACH ROW
UPDATE object
SET item_count = ( 
    SELECT COUNT(*) 
    FROM item
    WHERE object_id = NEW.object_id 
    )
WHERE object_id = NEW.object_id;

DEMO with complete triggers set.

5
  • Ok, if no one else comes out these way with another answer this seems to be the accepted one. Before accept to, one question. Using these 3 triggers, how would impact performance? Let's say that we are creating, updating, deleting milions of times a day the item record. So having a trigger or not having would it make a big difference you think? Commented Dec 28, 2022 at 5:57
  • This is related: stackoverflow.com/questions/38797444/… Commented Dec 28, 2022 at 6:07
  • @FedericoBaù Using these 3 triggers, how would impact performance? Of course, triggers decrease the performance for all data modifying queries. But I cannot predict how much it will be, you'd test this. This is related - no, the trigger by the link does not use data modifying queries. I think that the difference will be ~2-3 times in your case.
    – Akina
    Commented Dec 28, 2022 at 10:29
  • Why do COUNT(*) instead of the much more efficient item_count = item_count + 1?
    – Rick James
    Commented Dec 28, 2022 at 18:41
  • @RickJames It is more accurate. Even if an incorrect value is placed in the field due to some kind of failure, the next update will fix the incorrectness.
    – Akina
    Commented Dec 28, 2022 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.