Log In
Home
(0 votes) 4:23 pm
Can you give me the SQl necessary to delete all posts containing the word "apples?" For example, I can run
DELETE FROM wp_posts WHERE post_content LIKE '%apples%';
but that doesnt delete all rows from all tables related to wp_post.id
Help!
(mass delete works fine, but I figure that running the sql would be quicker)
Thanks!
4:32 pm
July 2, 2009
Offline
The simple SQL query would not be quicker just because it won't work correct. You must use API functions only (find more info 
Login to see this link).
4:29 pm
Thats part of my point :)
I attempt to Mass Delete about 700 posts daily, but on Thursdays, Fridays and Saturdays, the number of posts I have to delete increases well past 2500 posts for each day which tends to cause the script to fail and not stop processing.
And worse, the script doesnt stop running and tends to hang the server.
That led me to start looking for ways to do the same thing in MYSQL:
DELETE FROM wp_posts WHERE post_content LIKE '%apples%' <--- gets rid of the posts, then
DELETE FROM wp_postmeta WHERE NOT EXISTS (SELECT * FROM wp_posts WHERE wp_postmeta.post_id = wp_posts.ID) <--- removes the postmeta.
Those two SQL statements take about 5 seconds to run in total without hangs. But they do NOT remove the rows from the comments or the categories (doesn't decrease the category count either).
I looked at your reference and that would seem to work but if your plugin is based on it, I would assume that anything I attempt to do would fail in the same manner.
Is there anything you can do to keep the script from hanging? I dunno...have the script handle a much smaller amount of data and they restart itself? Like process 25 requests at a time according to what the user selects on the admin page and then restart...? That would probably work because for "smaller" amounts of data to process, it works flawlessly. So Im guessing that if you can help me "decrease" the amount of data its trying to process at the same time and then restart it? It would work 100% for me...
I appreciate any help you can give me in this effort.
4:41 pm
July 2, 2009
Offline
Sorry but I can do nothing. The script uses standard WordPress Login to see this link) you will see why it's so slow.
Thus if you're asking me if I can improve the original WordPress function, my answer is not, I can't do that. You can use a simple MySQL query to remove the group of post almost instantly, but it will work incorrectly because a lot of garbage (linked comments, categories, attachments, metadata etc) will be left in the database.
Furthermore, WordPress team may change the internal database structure at any time and your MySQL query will stop working or can even break the whole database. The only way to avoid these problems is to use standard WordPress API functions only.
All RSSMost Users Ever Online: 541
Currently Online:
17 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
ninja321: 84
s.baryshev.aoasp: 68
Freedom: 61
Pandermos: 54
MediFormatica: 49
B8europe: 48
Member Stats:
Guest Posters: 337
Members: 2818
Moderators: 0
Admins: 1
Forum Stats:
Groups: 1
Forums: 5
Topics: 1627
Posts: 8280
Newest Members:
bkc.tessier, igor.buzaev, jeremyboucher, lthompson2709, MAC, duty3canadaAdministrators: CyberSEO: 3913