We end up doing a nasty query to update all the urls and the stored procedures, which never really worked that well.
What would be nicer would be to update the single domains table as part of the logger saving process, as we're making a bulk update anyway with them. This would give us an easy to export single table we could use as a cache, or read only version of the API:
The other alternative would be to compress the values in redis using something liek snappy or lzw. It would be slight hike in CPU usage, but we'd likely be able to store all the domains in memory.
We have a list of updated urls that we make available at the link below:
https://www.thegreenwebfoundation.org/green-web-datasets/
Sadly, the way we update this table sucks.
We end up doing a nasty query to update all the urls and the stored procedures, which never really worked that well.
What would be nicer would be to update the single domains table as part of the logger saving process, as we're making a bulk update anyway with them. This would give us an easy to export single table we could use as a cache, or read only version of the API:
https://github.com/thegreenwebfoundation/greencheck-api/blob/master/src/Greencheck/Logger.php#L95
The other alternative would be to compress the values in redis using something liek snappy or lzw. It would be slight hike in CPU usage, but we'd likely be able to store all the domains in memory.
https://docs.redislabs.com/latest/ri/memory-optimizations/