Page 1 of 1
MySQL Monthly Table
Posted: Thu 03 Mar 2016 6:06 pm
by wbargent
Hi, I have a question about the SQL Monthly table. I currently have my station reporting every 5 mins and this is then logged into a monthly table on my MySQL Server however I would like to know what happened when the month is over. Is a new table created?
Re: MySQL Monthly Table
Posted: Thu 03 Mar 2016 6:14 pm
by steve
No, it all goes into the same table.
Re: MySQL Monthly Table
Posted: Thu 03 Mar 2016 7:29 pm
by wbargent
So what is the difference between the monthly and daily table?
Could I suggest this to be a feature in a later version? Table for each month.
Re: MySQL Monthly Table
Posted: Thu 03 Mar 2016 8:11 pm
by duke
wbargent wrote:
Could I suggest this to me a feature in a later version? Table for each month.
Why?
Re: MySQL Monthly Table
Posted: Thu 03 Mar 2016 11:27 pm
by philpugh
You will find that it is much easier to do analysis/summary work on the whole table. That said I also make a copy of the data into yearly tables.
Re: MySQL Monthly Table
Posted: Fri 04 Mar 2016 6:46 am
by steve
wbargent wrote:So what is the difference between the monthly and daily table?
The monthly table contains the minute-by-minute data corresponding to the monthly log files in MX (Mar16log.txt etc), and the daily table contains the daily summary data corresponding to the dayfile.txt file.
Could I suggest this to be a feature in a later version? Table for each month.
It's all in one table to make queries easier. MX will use the table itself at some point, so I won't be changing that. It's possible there might eventually be an option to create an additional table for each month, but I'm not sure of the benefits.
Re: MySQL Monthly Table
Posted: Fri 04 Mar 2016 8:18 am
by mcrossley
I can't see any possible benefits either. Splitting the data into tables would make handling it much more difficult in most instances.
If you really wanted to see month by month data without adding a where clause, then you could always create a view for each month.
Re: MySQL Monthly Table
Posted: Fri 04 Mar 2016 9:11 am
by wbargent
The main reason my I wan't to do this was due to the number of records that get inserted into my table; 1 every 5 mins. I thought it would be a good way to split the data up into smaller tables (not that they will they will be small) making them more manageable and easier to locate records using an application like phpmyadmin or Toad.
Re: MySQL Monthly Table
Posted: Fri 04 Mar 2016 12:02 pm
by mcrossley
The table is indexed on the date/time by default, so even with millions of records retrieving the date range you want should be fast.
My table only goes back to Jan 2010 with approx 500,000 rows, extracting the records for Jan 2016...
(8922 total, Query took 0.0023 sec)