What is the best way to handle millions of rows inside the Visits table?-CodePudding

According to this question, The answer is correct and made the queries better but does not solve the whole problem.

CREATE TABLE `USERS` (
 `ID` char(255) COLLATE utf8_unicode_ci NOT NULL,
 `NAME` char(255) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

There are only 5 rows inside the USERS table.

ID	NAME
C9XzpOxWtuh893z1GFB2sD4BIko2	...
I2I7CZParyMatRKnf8NiByujQ0F3	...
EJ12BBKcjAr2I0h0TxKvP7uuHtEg	...
VgqUQRn3W6FWAutAnHRg2K3RTvVL	...
M7jwwsuUE156P5J9IAclIkeS4p3L	...

CREATE TABLE `VISITS` (
 `USER_ID` char(255) COLLATE utf8_unicode_ci NOT NULL,
 `VISITED_IN` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY `USER_ID` (`USER_ID`,`VISITED_IN`),
 CONSTRAINT `VISITS_ibfk_1` FOREIGN KEY (`USER_ID`) REFERENCES `USERS` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The indexes inside the VISITS table:

Keyname	Type	Unique	Packed	Column	Cardinality	Collation	Null	Comment
USER_ID	BTREE	No	No	USER_ID VISITED_IN	3245 5283396	A A	No No

There are 5,740,266 rows inside the VISITS table:

C9XzpOxWtuh893z1GFB2sD4BIko2 = 4,359,264 profile visits
I2I7CZParyMatRKnf8NiByujQ0F3 = 1,237,286 profile visits
EJ12BBKcjAr2I0h0TxKvP7uuHtEg = 143,716 profile visits
VgqUQRn3W6FWAutAnHRg2K3RTvVL = 0 profile visits
M7jwwsuUE156P5J9IAclIkeS4p3L = 0 profile visits

The time is taken for queries: (Seconds will change according to the number of rows)

SELECT COUNT(*) FROM VISITS WHERE USER_ID = C9XzpOxWtuh893z1GFB2sD4BIko2

Before applying Rick James' answer, The query took between 90 to 105 seconds
After applying Rick James' answer, The query took between 55 to 65 seconds

SELECT COUNT(*) FROM VISITS WHERE USER_ID = I2I7CZParyMatRKnf8NiByujQ0F3

Before applying Rick James' answer, The query took between 90 to 105 seconds
After applying Rick James' answer, The query took between 20 to 30 seconds

SELECT COUNT(*) FROM VISITS WHERE USER_ID = EJ12BBKcjAr2I0h0TxKvP7uuHtEg

Before applying Rick James' answer, The query took between 90 to 105 seconds After applying Rick James' answer, The query took between 4 to 8 seconds

SELECT COUNT(*) FROM VISITS WHERE USER_ID = VgqUQRn3W6FWAutAnHRg2K3RTvVL

Before applying Rick James' answer, The query took between 90 to 105 seconds
After applying Rick James' answer, The query took between 1 to 3 seconds

SELECT COUNT(*) FROM VISITS WHERE USER_ID = M7jwwsuUE156P5J9IAclIkeS4p3L

Before applying Rick James' answer, The query took between 90 to 105 seconds
After applying Rick James' answer, The query took between 1 to 3 seconds

As you can see before applying the index, It was taken between 90 to 105 seconds to count the visits of a specific user even if the user has a few rows (visits).

After applying the index things became better but the problem is:

If I visit the C9XzpOxWtuh893z1GFB2sD4BIko2 profile, It will take between 55 to 65 seconds to get profile visits.
If I visit the I2I7CZParyMatRKnf8NiByujQ0F3 profile, It will take between 20 to 30 seconds to get profile visits.
Etc...

The user who has a few rows (visits) will be lucky because his profile will load faster.

I can ignore everything above and create a column inside the USERS table to count the user visits and increase it when catching a new visit without creating millions of rows but that will not be working with me because I allow the user to filter the visits like this:

Last 60 minutes
Last 24 hours
Last 7 days
Last 30 days
Last 6 months
Last 12 months
All-time

What should I do?

CodePudding user response：

This not be the answer, but a suggestion.

If they do not require real-time data, Can't we run a scheduler and insert these into a summary table every x minutes. then we can access that summary table for your count.

Note: We can add a sync time column to your table if you need a time-wise login count. (Then your summery table also getting increased dynamically)

Table column ex:

PK_Column, user ID, Numb of visit, sync_time

We can use asynchronous (reactive) implementation for your front end. That mean, Data will load after some time, but the user never will experience that delay in his work.
create a summary table and every day at 12.00 AM run a job and put the user wise and date wise last visited summery into that table.

user_visit_Summary Table: PK_Column, User ID, Number_of_Visites, VISIT_Date

Note: Create indexes for User ID and the Date fields

When you're retrieving the data, you're going to access it by a DB function

Select count(*)    (Select Number_of_Visites from VISITS 
where user_id = xxx were VISIT_Date <= ['DATE 12:00 AM' -1]   PK_Column desc limit 1)  as old_visits
where USER_ID = xxx and VISITED_IN > 'DATE 12:00 AM';

CodePudding user response：

The problem is that you are evaluating, and continually re-evaluating, very large row counts that are actually part of history and can never change. You cannot count these rows every time, because that takes too long. You want to provide counts for:

Last 60 minutes

Last 24 hours

Last 7 days

Last 30 days

Last six months

All-time

You need four tables:

Table 1: A small, fast table holding the records of visits today and yesterday

Table 2: An even smaller, very fast table holding counts for the periods 'Day before yesterday ("D-2") to "D-7", field 'D2toD7', the period 'D8toD30', 'D31toD183' and 'D184andEarlier'

Table 3: A table holding the visit counts for each user on each day

Table 4: The very large and slow table you already have, with each visit logged against a timestamp

You can then get the 'Last 60 minutes' and 'Last 24 hours' counts by doing a direct query on Table 1, which will be very fast. ‘Last 7 days’ is the count of all records in Table 1 (for your user) plus the D2toD7 value (for your user) in Table 2. ‘Last 30 days’ is the count of all records in Table 1 (for your user) plus D2toD7, plus D8toD30. ‘Last six months’ is Table 1 plus D2toD7, plus D8toD30, plus D31toD183. ‘All-time’ is Table 1 plus D2toDy, plus D8toD30, plus D31toD183, plus D184andEarlier.

I’d be running php scripts to retrieve these values – there’s no need to try and do it all in one complex query. A few, even several, very quick hits on the database, collect up the numbers, return the result. The script will run in very much less than one second.

So, how do you keep the counts in Table 2 updated? This is where you need Table 3, which holds counts of visits by each user on each day. Create Table 3 and populate it with COUNT values for the data in your enormous table of all visits, GROUP BY User and Date, so you have the number of visits by each user on each day. You only need to create and populate Table 3 once. You now need a CRON job/script, or similar, to run once a day. This script will delete rows recording visits made the day before yesterday from Table 1. This script needs to:

Identify the counts of visits for each user the day before yesterday
Insert those counts in Table 3 with the ‘day before yesterday’ date.
Add the count values to the ‘D2toD7’ values for each user in Table 2.
Delete the 'day before yesterday' rows from Table 1.
Look up the value for (what just became) D8 for each user in Table 3. Decrement this value from the ‘D2 to D7’ value for each user.
For each of the ‘D8toD30’, ’D31toD183’ etc. fields, increment for the day that is now part of the time period, decrement as per the day that drops out of the time period. Using the values stored in Table 3.

Remember to keep a sense of proportion; a period of 183 days approximates to six months well enough for any real-world visit counting purpose.

Overview: you cannot count millions of rows quickly. Use the fact that these are historical figures that will never change. Because you have Table 1 for the up-to-the-minute counts, you only need to update the historic period counts once a day. Multiple (even dozens of) very, very fast queries will get you accurate results very quickly.