Author Topic: mySQL tips for WeatherCat  (Read 856 times)

UncleBuck

  • Strong Breeze
  • ***
  • Posts: 114
    • IVICTORI276
    • Point Cook Weather
  • Station Details: Oregon WMR200, MAC OS X 10.10, WeatherCat V2
mySQL tips for WeatherCat
« on: June 14, 2013, 05:37:52 AM »
After some initial teething problems I got WC to work really well with mySQL but there are a couple of things you will need to do to get the most out of it.

One thing I noticed is that there are no indexes on the table and if you perform a join and query the db with a semi complex select statement, it can take quite a while to complete.

One thing I did to improve performance was to create a primary index on each of the tables using the Time column as the key.
This reduced the runtime for the following query down from over 180 seconds to 262ms.
Code: [Select]

SELECT Convert_tz(WeatherData.time, 'UTC', 'Australia/Melbourne') AS Local_Time,
       Temperature_C,
       Dewpoint_C,
       Humidity,
       Pressure_mB,
       RainRate_mm,
       RawRain_mm,
       UV_index,
       TemperatureInt_C,
       HumidityInt,
       Windspeed_kmh,
       Winddir,
       WindspeedAv_kmh,
       WinddirAv,
       WindspeedGust_kmh,
       WindDirGust
FROM   WeatherData
       INNER JOIN WindData
               ON WeatherData.time = WindData.time
WHERE  Hour(Convert_tz(WeatherData.time, 'UTC', 'Australia/Melbourne')) = Hour(
       Now())
   AND Day(Convert_tz(WeatherData.time, 'UTC', 'Australia/Melbourne')) = Day
       (Now())
   AND Month(Convert_tz(WeatherData.time, 'UTC', 'Australia/Melbourne')) =
       Month(
       Now())
   AND Year(Convert_tz(WeatherData.time, 'UTC', 'Australia/Melbourne')) =
       Year(Now());

I would also like to use this topic as a place for people to provide some SQL scripts such as Select statements, Views and Triggers etc. so join in if you feel you have come up with something others can benefit from or even if you are having problems and just need someone bounce some ideas off.

I am far from an SQL expert but I am sure there are others out there that would be able to help you (and me) out.

Col.