Curious if anyone has looked into adding additional Indexes for alldata? I note the DateTime is the primary key, but also the only index that exists. Just wondering if there would be any speed to be gained as I've got about 1,005,000 rows in my database and occasionally things seem to take a little bit to load.
Thanks!
MySQL tuning?
- Jachym
- Site Admin
- Posts: 1686
- Joined: Fri Aug 18, 2017 10:12 pm
- Location: Brno, Czech Republic
- Station model: WH1080
- Software: Meteobridge
- Contact:
Re: MySQL tuning?
Hi,
not sure, the thing is, in 99% of cases, the filtering is based on date/time
not sure, the thing is, in 99% of cases, the filtering is based on date/time
-
- Professional
- Posts: 431
- Joined: Tue Feb 27, 2018 10:56 pm
- Location: OKLAHOMA, USA
- Station model: Ecowitt WS90
- Software: Meteobridge/GW2000B
- Contact:
Re: MySQL tuning?
Actually there are two times. First your local time and second is UTC time. Really the only thing you can do is re-index on the UTC time if your have added missing data which gets added to the end of the database file or manually corrected (edited) erroneous data which in that case you would not need to re-index. You need at least have a basic knowledge of MYSQL. See http://www.meteotemplate.com/web/wiki/wiki1GB.php for a basic understanding before attempting.
I also have over a million lines of data and from time to time noticed a lag which is basically my host server being heavily used on a shared server.
The other thing I have done is added more memory (at a cost) to my server account as I have over 4gb of files mostly my weather cam files taking up the bulk. I will be cutting it off at a year of webcam files in the near future.
Andy
I also have over a million lines of data and from time to time noticed a lag which is basically my host server being heavily used on a shared server.
The other thing I have done is added more memory (at a cost) to my server account as I have over 4gb of files mostly my weather cam files taking up the bulk. I will be cutting it off at a year of webcam files in the near future.
Andy