Hello Radhoo, hello Ally,
The table with raw-values you have will be needed allways to store raw-data (maybe it can be compressed).
From this table you will create views or other (processed) tables. But first of all, we need to know what data we need for what to display.
As a basis of discussion, I inclue the structure of my (quickly made) table.
My device reports to this.
For example, first I had the “timestamp” for every entry. The I realized, I’m much faster in selcting and grouping when I use a integer “timestamp_unix” instead.
Grouping I use in views, for example 10min-averages or 1h-averages over a given timespan.
mysql> SHOW CREATE TABLE tab_values\G
Create Table: CREATE TABLE tab_values (
id int(11) NOT NULL AUTO_INCREMENT,
created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
timestamp timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
timestamp_unix int(11) DEFAULT NULL,
uradid varchar(16) DEFAULT NULL,
p varchar(16) DEFAULT NULL,
ts int(11) DEFAULT NULL,
inv int(11) DEFAULT NULL,
ind int(11) DEFAULT NULL,
s1t double DEFAULT NULL,
cpm int(11) DEFAULT NULL,
c int(11) DEFAULT NULL,
remote_ip varchar(45) DEFAULT NULL,
forwardfor_ip varchar(45) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8