lambooni
6 months ago

Dealing with model aggregate sum/count for large tables.

Posted 6 months ago by lambooni

Counts and sums are expensive operations when large tables are involved. I have several that are well over a million rows and performing these counts and sums on the fly is very slow.

I need the results in both the front end as information to the user, and in the back end for logic.

The common thinking on this is to save the results to a table, cache or other memory. Can anyone point me in the direction of the best way to get this setup, or if there is a package available? I think I need...

  • Required count rules/queries to be saved somewhere.
  • A way to execute the new count. This could be as simple as adding the eloquent count/sum after every bit of code that updates that table, but I feel there must be a better way. Would a model observer be a good use for this? Is there a way I can have a central observer for all models and not have to create something new for every time. Ideally I would like to be able to add the required count/rule to the table and for all this to happen automatically.
  • Generally the counts can be performed in a queue, but there may be times when this needs to happen synchronously.

Or maybe I am going completely the wrong direction on this and there is a better way?

Thanks a lot.

Please sign in or create an account to participate in this conversation.