I haven't touched indexes, just the number type on the columns.. I went about this by getting unique values for the column and then used that to optimise the column.
The only exception was the winddir column, it had decimal places, but a fraction of a degree doesn't seem worth having. On Tue, 18 Nov 2025 at 02:51, Pablo Sanchez <[email protected]> wrote: > That's impressive! > > The key is to ensure each data type for all Use Cases captures all data. > > On my mental to-do is to enable query tracing to ensure when needed, > supporting indexes are available. > --- > pablo > > On November 17, 2025 10:18:13 John Smith <[email protected]> > wrote: > >> I spoke too soon, I changed the winddir and windgustdir fields to small >> ints, saving 2 bytes per value which may not sound like much but it adds up >> over time... >> >> I also changed the humidity columns to be tiny ints as well... >> >> The above changes saved a further 20MB, and the archive table is just a >> little over 170MB in size... >> >> On Tue, 18 Nov 2025 at 01:05, John Smith <[email protected]> >> wrote: >> >>> I have always been hesitant to mess with the weeWX DB in case I broke >>> it, but I ended up modifying the archive table and made a custom schema to >>> match just in case. >>> >>> It turned out more fruitful than I expected as I dropped the columns >>> that were only full of NULLs. Worst case I need to re-add them and then the >>> NULLs will be re-added too. >>> >>> The extended schema has some interesting columns, such as 5 regarding >>> hail, but does anyone know of any hail sensors? >>> >>> The archive table went from 1.1GB down to 420MB in the process... >>> >>> The only problem after doing this was weeWX exited on start because I >>> didn't set a default value on the new columns I added, however after >>> setting default values weeWX was happy again. >>> >>> Then I changed the usUnits and interval columns from Int to tiny int, >>> what that means is instead of MariaDB allocating 4 bytes per value it only >>> uses 1 byte per value. I'm not sure setting those columns as ENUM type >>> would reduce things further, but the usUnits column only has the number 17 >>> stored and the interval column is always set to 5... >>> >>> It was then I noticed that nearly all the columns were of type double, >>> which seems like over kill to me as most of the time there really is only a >>> need for 1 decimal place, not 16... >>> >>> So I altered all the double columns and changed them to floats, since >>> doubles use 8 bytes per value verses 4 bytes for floats and this again >>> greatly dropped the table size down to 190MB all up this works out to be >>> almost 1/10th of it's original size. >>> >>> At this point I think that's the extent that I can take things and >>> thought others might like to know that trimming the archive table, without >>> loosing data, but recover a lot of storage space which will then also speed >>> up other things like backups and reduce the space needed to store backups. >>> >> -- >> You received this message because you are subscribed to the Google Groups >> "weewx-user" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To view this discussion visit >> https://groups.google.com/d/msgid/weewx-user/CAGTinV5%3DgG_GkeSucYVX%3DrQSP3%3DL5odJkqvfK%2BFQR3ZCRuVTkQ%40mail.gmail.com >> <https://groups.google.com/d/msgid/weewx-user/CAGTinV5%3DgG_GkeSucYVX%3DrQSP3%3DL5odJkqvfK%2BFQR3ZCRuVTkQ%40mail.gmail.com?utm_medium=email&utm_source=footer> >> . >> > > -- > You received this message because you are subscribed to the Google Groups > "weewx-user" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion visit > https://groups.google.com/d/msgid/weewx-user/19a9283a788.2848.44b74f35a7a433c111dbfaee4cca1c2d%40hillsandlakes.com > <https://groups.google.com/d/msgid/weewx-user/19a9283a788.2848.44b74f35a7a433c111dbfaee4cca1c2d%40hillsandlakes.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "weewx-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion visit https://groups.google.com/d/msgid/weewx-user/CAGTinV6Eq-R9p_5%2BgrMSRxByTXa-O2CQjCUersr-A2Mj9zy3_g%40mail.gmail.com.
