How to delete posts (pages) based on certain slug in their URLs in phpmyadmin

  • isitegate

    (@isitegate)


    3 years, 4 months ago

    I really need help with few of my websites which have thousands of pages.

    Now, I want to be able to write a command in SQL to say,

    Delete All Pages if their urls DO **NOT** conian one of these slugs ([an array of slugs])

    For example.. I want to keep pages which have this part of their url […mydomain…] + “/england/gl/recruitment-agency/” + […another part of the url…]

    This is one slug I want to keep, but I have about 40 of them.

    Other pages which don’t contain those slugs they should be removed.

    So, what is the command that will do the job for me?

    I really appreciate any help or hints as my websites crashing down because of having too many pages.

    Thank you in advance.

    The page I need help with: [log in to see the link]

Viewing 5 replies - 1 through 5 (of 5 total)
  • corrinarusso

    (@corrinarusso)

    3 years, 4 months ago

    @isitegate

    You are taking a big risk using SQL for this. It may sound like a simple
    delete * from _terms or _postsmeta where slug like ‘%xxxxx%’
    but it is not. You don’t know what else this will be affecting, etc, etc, etc.

    Backup your data, and try this plugin first – it says it can bulk delete based on path :
    https://en-ca.wordpress.org/plugins/bulk-delete/

    > my websites crashing down because of having too many pages.

    I sincerely doubt this.
    Or, if true then recommend using a VPS, etc so that this does not happen.
    It’s more likely you have malware – see attached :

    View post on imgur.com

    Thread Starter isitegate

    (@isitegate)

    3 years, 4 months ago

    @corrinarusso thank you very much for the reply.

    Thank you for the suggestion about malware, ill run a scan shortly.

    As for it is risky, even if we selected wp_posts and wp_postmeta tables only not all tables?

    > I sincerely doubt this. i have 18000+ pages, don’t you think this can crash a wordpress site?

    corrinarusso

    (@corrinarusso)

    3 years, 4 months ago

    If you wanted to move forward with this approach ……… I personally would
    – use the plugin, or :
    – create a copy of the entire database
    – select the ID and Post_IDs of the path I am looking for
    https://codex.wordpress.org/Database_Description#Database_Diagram
    – delete * from posts postmeta where ID = ‘xxx’

    But that post_id will/may be shared by other plugins or resources that you are not accounting for. I would run a tonne of select statements to see the data I am removing before I actually remove it. Before all this, I may even insert a string into the slug so I can visualize the data first – like change :
    /this-is-your-path/
    to
    /DELETE-ME-this-is-your-path/
    for example.

    But again – there is a configurable plugin for this already :
    https://wordpress.org/plugins/bulk-delete/

    > i have 18000+ pages, don’t you think this can crash a wordpress site?

    Not really. From what perspective ?
    There is load on the db, but if I hit one of your pages, I’m not loading all 18k posts am I.

    Did you look at your apache and php error logs ?

    Thread Starter isitegate

    (@isitegate)

    3 years, 4 months ago

    Thanks again, i have contacted the server support to look into the error logs, will update here later.
    Yes you are right, we are only hitting on one page not 18000.
    so there is no limit to howw much wordpress databasecan handle, also the limit of traffic as these site get too many thousadns hits per day.

    corrinarusso

    (@corrinarusso)

    3 years, 4 months ago

    There is (sort of) a limit – but it’s nowhere close to 18,000. It’s more like 2,000,000.
    Limits come from the rack mostly – CPUs, memory, swap, disk space, etc. Talk to your host about the best plan for your traffic. Also, the allocation of resources to your website should be flexible to accommodate for your site and spikes in traffic, i.e.
    https://www.rackspace.com/services/managed-services

    My my most successful client site has about 5,000 unique pieces of content, and generally 50,000 to 60,000 visitors per month.
    No issues.

    Lastly, removing a lot of pages will have significant impact on your SEO if that is of concern to your business.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘How to delete posts (pages) based on certain slug in their URLs in phpmyadmin’ is closed to new replies.