If you are developing an event-based application that handles many requests from different users, you most likely want to count distinct user action within a sliding window or a specified time range.
One of the quickest ways to count distinct user is to prepare an SQL like SELECT count(distinct user) from ACTION_TABLE. But, this might be expensive if there are millions of records produced in real time.
One of the benefits of InfluxDB is the ability to store raw events, which might come in at varying intervals, as an irregular time series. However, irregular time series present some unique challenges, and in some cases, common operations on the data simply will not work. Fortunately, InfluxDB allows you to convert an irregular time series to a regular one on the fly by calculating an aggregate of individual values for arbitrary windows of time. This gives you the best of both worlds when capturing events from your systems and working with that data.
We can take a look at a few actual data points in order to get a better understanding of what considerations need to be made when working with irregular time series. For the sake of example, we’ll use five data points, and give them values of 10, 20, 30, 40, and 50.
I created > one billion InnoDB tables in MySQL 8.0 (tables, not rows) just for fun. Here is the proof:
$ mysql -A
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1425329
Server version: 8.0.12 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> select count(*) from information_schema.tables;
+------------+
| count(*) |
+------------+
| 1011570298 |
+------------+
1 row in set (6 hours 57 min 6.31 sec)Yes, it took 6 hours and 57 minutes to count them all!
