Best practice to get summary/filters from a large SQL table

87
September 11, 2021, at 08:30 AM

In our system, a user can be of 2 types

  1. Client
  2. Affiliate client

The client can have one listing and Affiliate clients can have multiple listings. So we get calls against listings and we store them in a table called call logs table.

Now this table has ~5 million records and this table grows every minute. A call log belongs to a listing and a user(client/affiliate). So a call log can have (a client id and a listing id) OR (an affiliate client id and listing id).

We show filters on the call logs page an admin portal to filters the call logs. Admin can filter the call logs by Clients, Affiliates Client, and Listings.

This is how I get clients, affiliates, and listings from the call logs table to make filters.

select * from `call_logs` order by `call_logs`.`id` asc limit 500 offset 0

So after getting the 500 call logs, I extract

  1. Users with type client and store them in a variable named client filter array.
  2. Users with type affiliate client and store in a variable called affiliate filter array.
  3. Unique listings and store in a listings filter array.

After looping through the whole table of call_logs, because I chunk up the table to 500 rows per loop, I displayed those filters on the admin portal page.

Issue/Problem: Because the table is large, so separation of users on the base of type(Client and affiliate client) and listing take a long time, so the admin has to wait for page loading. I have also tried to load those filters after the page is successfully loaded via ajax, but most of the time its takes too long.

I can't use groups by user types like user_id, affiliate_client_id, or listing_id. Because if a group by affiliate_client_id, so the affiliate can have multiple listings with different calls, so it will pick on one call, the same thing for listing.

Answer 1

you need an index on user_id and call_fee columns and this query calculate the call fee for all the users in one query:

SELECT `user_id`,sum(`call_fee`) 
FROM `call_logs` 
GROUP BY `user_id`;
Rent Charter Buses Company
READ ALSO
Right to Left Icicle Tree

Right to Left Icicle Tree

How can I mirror the d3 tree? I have use these code https://observablehqcom/@d3/zoomable-icicle but I can't put the father at right

63
How to reset row indexes on filtered values in React-Table?

How to reset row indexes on filtered values in React-Table?

I'm trying to implement a feature in my table where I need the current row index after a filter is applied in React-Table

126
PHP Page Navigation using arrays and foreach, trouble with category level = active

PHP Page Navigation using arrays and foreach, trouble with category level = active

So, in another post, CBroe was ever so helpful, along with @MeathCoder to illustrate the needed changesI've since been able to do the whole thing and it looks amazing, but I'm havign trouble setting the category to active! Below is my code and not sure how I'd change...

101