MySQL | Automatically count related tables as a counter (one to many)-CodePudding

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.

CodePudding user response：

MySQL alone doesn't have any automatic way of doing what you describe. It's up to you to implement a solution, or use an additional technology with MySQL.

I can think of at least the following solutions:

Use triggers to maintain your counter. Increment on INSERT, decrement on DELETE. You might run into throughput problems if your rate of concurrent inserts and delete is very high.

create trigger ti after insert on item
for each row 
  update item_counter 
  set counter = counter   1
  where object_id = NEW.object_id;

create trigger td after delete on item
for each row
  update item_counter 
  set counter = counter - 1
  where object_id = OLD.object_id;

Demo: https://dbfiddle.uk/tgJM0I4m

Use a materialized view. PlanetScale recently offered a product (in beta as of November 2022) that does something like a materialized view, but it's more complex than that. Read their description: https://planetscale.com/blog/how-planetscale-boost-serves-your-sql-queries-instantly
Use a data warehouse. An OLTP database like MySQL typically stores tables as rows, so it's optimized for CRUD type queries. A data warehouse or OLAP database stores tables as columns, so aggregate queries like the one you describe are its strength. But this means setting up a separate database technology, and syncing your MySQL data to the data warehouse continually.
WARP is a MySQL 8.0 storage engine that seems to combine both a column-store and materialized views. See https://www.leapdb.com/ I worked with the developer who created WARP, he's brilliant.