• Purchasing a license grants permission to use the software and to get Tech Support. A license purchase doesn't grant customization support.

Purge guest images older than specific date with SQL query

mkerala2

Network license
Beta tester
Joined
Jun 30, 2014
Messages
449
Points
105
If you are looking to reclaim some space on your server by deleting those guest images without any views, run the below query to force expire them in database which will get deleted cleanly.

Code:
update images set image_expiration_date_gmt='2018-11-10 00:00:00' WHERE image_date <= '2017-11-14 00:00:00' and image_user_id is NULL and image_views <=500;
image_expiration_date_gmt='2018-11-10 00:00:00': Enter a date in past
image_user_id is NULL: Delete only guest images
image_date <= '2017-11-14 00:00:00' : Till which date images should be deleted
image_views <=500 : Avoid deleting images with greater than 500 views

No matter how many images are there, the system will slowly and cleanly remove them in batches without causing much load on the system. Both database entry and image file will get deleted.
 
Last edited:

schubert24

Network license
License owner
Joined
Jul 25, 2019
Messages
6
Points
53
Location
ZĂĽrich, Switzerland
Website
dyscoverit.com
Little mod to make it dynamic:

UPDATE `chv_images`
SET `image_expiration_date_gmt` = Date(Date_Sub(Now(), INTERVAL 1 DAY))
WHERE `image_date` <= Date(Date_Sub(Now(), INTERVAL 12 MONTH))
AND `image_user_id` is NULL
AND `image_views` <= 1;

I'll add this to my housekeeping bot (see my other post)