Mysql count of large tables (laravel)?

Question

MySQL

Good afternoon! Guys, I googled a lot and as far as I understood, there is no simple solution. Indexes don't help, can you give me a hint.
There is a table And with some records. I break them by pagination 10 per page. One record from table A can have 100k, 400k, 1kk or more records of records from table B attached .

I need to display a counter, how many records in table B have data attached to a record in table A.
Now I filter them, including dates, but sometimes I need to get data for all time. The request is being processed for quite a long time, how can we speed up this process as much as possible?

I have 2 ideas, load the page without a counter and load the counter asynchronously, then the page will load quickly, but the actual data will still be unavailable for quite a long time. And the other option, which is essentially the same, is to queue the request and send data through the web socket, but the problem will be the same as in the first option.

Thank you!

Answer 1

foreign keys