I just wanted to do this because I like a neat db in case i want to mess
around with it myself. Just makes it a bit more clear when staring at a
table when you see numbers and not just a lot of NULL.
Also I had hoped it would flatten out the cpu spike that it has every 5
minutes because it's just high enough to trigger the cpu fan to speed up
and become audible :-)
But it didn't really make any difference.
If anyone else does decide to tidy up their db you might need this bit of
sql as the day tables don't seem to get cleaned up by 'weectl database
drop-columns'
But make sure to not execute the results blindly and take out the
__metadata table. But this checks all the archive_day tables and sees if
the corresponding column exists in archive.
Do be sure to stop/restart weewx.
```
mysql -N -B -e "
SELECT CONCAT('DROP TABLE \`weather\`.\`', t.TABLE_NAME, '\`;') AS drop_stmt
FROM information_schema.TABLES t
LEFT JOIN information_schema.COLUMNS c
ON c.TABLE_SCHEMA = 'weather'
AND c.TABLE_NAME = 'archive'
AND c.COLUMN_NAME = SUBSTRING(t.TABLE_NAME, CHAR_LENGTH('archive_day_') + 1)
WHERE t.TABLE_SCHEMA = 'weather'
AND t.TABLE_NAME LIKE 'archive_day\\_%' ESCAPE '\\\\'
AND c.COLUMN_NAME IS NULL
ORDER BY t.TABLE_NAME;
" > drop_archive_day_orphans.sql```
Op vrijdag 27 februari 2026 om 19:59:47 UTC+1 schreef DR:
> That is in alignment from what I recall some time ago.
>
>
> Database engines have gotten much smarter and do a lot which I'm no
> longer aware of. I recall the days when we packed 8 binary values into
> one word and masked it to get what we wanted. Obviously won't work with
> numeric values but shows the lengths we were taught to do in the infancy
> of software. And magic which can occur behind the scenes is often of no
> concern to programmers now. I recall when something termed "Rushmore"
> techniques were used to speed indexed services after the first reference.
>
> Thank you for sharing the info you accumulated about speed and storage.
>
> It keeps this forum fun and fact filled. Dale
>
>
>
--
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/a039b73d-a0f1-46da-b1be-a5cb94c9380cn%40googlegroups.com.
