We haven't yet run this on the Live site...
Looking at Test we realized it shaved off too much.
One thing to note is that 'wpcf-dealer-address' is absolutely the only address field the site is using. The others listed will be deprecated.
Another important thing I should have made clear is that if a user is in the United States and that user does not opt-in to Google's Geo-Locate, then an IP API service will return their 5 digit zip code to the Toolset Map View search field and submit on document ready.
There are quite a lot of codes in the US, so keeping all 5 digit zip codes we get in the cache would save us google maps API usage fees.
Would you be able to adjust the sql to accommodate that?
Okay I understand your request, it would be preferred to maintain all cached entries for 5-digit US zip codes. I will ask my 2nd tier team if this customization is possible with a SQL modification, and let you know what I find out. It's fairly custom, but I also think it would be fairly useful for similar sites.
Okay I have an update to the SQL script. This should be run in a test environment after you pull down the full database from the live site:
DELETE wp_toolset_maps_address_cache
FROM wp_toolset_maps_address_cache
LEFT JOIN wp_postmeta
ON wp_toolset_maps_address_cache.address_passed = wp_postmeta.meta_value
AND wp_postmeta.meta_key IN ('wpcf-dealer-address','wpcf-dealer-address-contact','wpcf-address-contact-crm-bdt','wpcf-address-contact-crm-ss','wpcf-address-contact-hd-bs','wpcf-address-contact-hd-rc','wpcf-address-contact-hd-ss')
WHERE wp_postmeta.meta_value IS NULL
AND wp_toolset_maps_address_cache.address_passed NOT REGEXP '^[0-9]{5}$'
...or if you want to go ahead and delete from the deprecated address fields as well:
DELETE wp_toolset_maps_address_cache
FROM wp_toolset_maps_address_cache
LEFT JOIN wp_postmeta
ON wp_toolset_maps_address_cache.address_passed = wp_postmeta.meta_value
AND wp_postmeta.meta_key IN ('wpcf-dealer-address')
WHERE wp_postmeta.meta_value IS NULL
AND wp_toolset_maps_address_cache.address_passed NOT REGEXP '^[0-9]{5}$'
That should preserve all 5-digit US zip code entries. Please let me know the results after running this script in an updated test environment.
My issue is resolved now. I would like to strongly recommend expanding on the map cache controls / automation in the settings panel. We're probably not the only Toolset user walking the tightrope of exceptionally high usage while trying to avoid google maps API hits. That said, we really appreciate you taking the time to work up the SQL specifically for our situation. It's definitely a workable solution. Many Thanks!
