The number of millions of users from the database in real time?

Question

What is an effective way to select a counter (*) from the database to count registered users and create a counter in real time (or almost real time)? I don’t want to run this request every second (per million users) .. I thought about starting every hour, and then I calculated new users and created a settlement counter. Things like tracking visitors will not work, bc they come from a large number of sites, should use this user database. Any suggestions?

Answer 1

Which DB? Both SQL Server and Oracle, for example, can support counting for you in an indexed (materialized) view. For example, on SQL Server:

create view Metric with schema binding as select count_big(*) as RegisteredUserCount from dbo.users where ...; create clustered index cdxMetric on Metric(RegisteredUserCount); 

The engine will keep this counter accurate for you, see Improving Performance with SQL Server 2008 Indexed Views :

 select RegisteredUserCount from dbo.Metric with (NOEXPAND); 

Alternatively, you can cache this result and get automatic revocation, see LinqToCache .

If you are using a system with MySQL support, then probably the best way is to count it once, save the account in memcached and update it when registering a new user.

Answer 2

Since this is something that will be executed frequently, I would recommend keeping a table of metrics that update as users are added.

 Create Table Metric ( RegisteredUserCount int not null Default ( 0 ) , ... ) 

You can then create a series of triggers in the Users table that updates the Metric table or includes it in a stored procedure or code used to insert / update / delete users. Each time, so often, you can check the verification of numbers in the Metric table with the actual number of registered users.

Answer 3

It looks like you just want to assign a user id to the next user. In this case int / bigint will not increase automatically?

Answer 4

You can count users once, and then create stored procedures that subtract from the user count when members are removed and added to the number when members are added. I also create a chron task to run on a schedule that is convenient for you, to recalculate the user table and update the counter.