Pruning unused redirects #2123

Open
balohmatevz opened this Issue Jul 20, 2016 · 2 comments

Projects

None yet

3 participants

@balohmatevz

Seeing as the mass-delete plugin is hosted on this github repository, I thought this might be the best place to post this. Apologies if it's not.

What I would like is a pruning plugin - a plugin which removes no longer used redirects for privacy reasons. Who knows what I've shortened 2 years ago to share once and then never again.

Since I've done manual pruning myself, I thought it might be helpful to simply share the two SQL queries I used for anyone with a similar problem or intention to make the plugin. I did it through two steps:

  • create table with backups
  • remove entries from main table.

These two queries remove all URLs which have not been clicked on in the last N days. By default it's 30 days. To change it to another number, edit the "30" in "INTERVAL 30 day". All deleted URLs remain in the deleted_urls table, where they can be recovered from later. If you're 100% confident that you don't need the deleted urls, you can delete the deleted_urls table afterwards.

CREATE TABLE deleted_urls as ( SELECT * FROM yourls_url WHERE keyword IN ( SELECT shorturl FROM ( SELECT shorturl, max(click_time) as lastclick FROM yourls_log GROUP BY shorturl ) a WHERE a.lastclick < DATE_SUB(NOW(), INTERVAL 30 day) ORDER BY lastclick DESC ) ORDER BY clicks DESC )

and then to delete:

delete from yourls_url where keyword in (select keyword from deleted_urls)

@seventhsite

Also, will good if I can delete only links without clicks and less than, for example, 2 clicks also.
But another problem, as I understand, there is no way to reuse free urls again. If I delete yourls.org/2Mz, the script don't create it again by itself.

@dgw
dgw commented Jul 20, 2016 edited

I wouldn't call it a problem that deleted URLs aren't recreated. It's better to have YOURLS report that the keyword isn't found than to redirect to something other than was originally intended, if the link has been shared somewhere.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment