5th.li deleting #linkGone links to implement

5thli_linkGone_delete.sql

# select urls / #linkGone links which werent accesed since 5 years
SELECT * FROM (
    SELECT log.shorturl, MAX(log.click_time) AS most_recent_click_time
    FROM yourls_log as log
        INNER JOIN yourls_url as url
        ON log.shorturl=url.keyword
    WHERE url.title = 'http://5th.li/#linkGone'
    GROUP BY log.shorturl
) AS sub
WHERE sub.most_recent_click_time < DATE(NOW()) - INTERVAL 5 YEAR
ORDER BY most_recent_click_time DESC


# delete logs with no urls
DELETE FROM yourls_log WHERE shorturl NOT IN (
    SELECT keyword
    FROM yourls_url
)

# reset lastid in yourls so ids get reused
UPDATE  `yourls_options` SET  `option_value` =  '1'
WHERE  `yourls_options`.`option_id` = 3
AND  `yourls_options`.`option_name` =  'next_id';