My sql out of memory

Hey guys i have this site and around 4,000 ads in sql. Below is the function for deleting expired ads. Is there a way i can have it check and delete like 250 records at a time and not try to check it all at one time? Fatal error: Out of memory (allocated 117964800) (tried to allocate 71 bytes) in /htdocs/www/wp-includes/meta.php on line 574 i , tried “posts limit 0,1000” and the error goes away but doesn’t delete the expired ads. Thanks

	function a2p_delete_expired_posts() {
		global $wpdb, $Auction2Post;
		
		$posts = $wpdb->get_results("SELECT ID FROM " . $wpdb->prefix . "posts");
		
		foreach ($posts as $post) {
			
				if (a2p_is_expired_post($post->ID)) {

					// let's get rid of the "bloat" when the ad expires//
					
					delete_post_meta($post->ID, 'a2pBestOfferEnabled');
					delete_post_meta($post->ID, 'a2pBidCount');
					delete_post_meta($post->ID, 'a2pBuyItNowAvailable');
					delete_post_meta($post->ID, 'a2pGalleryURL');
					delete_post_meta($post->ID, 'a2pQuantity');
					delete_post_meta($post->ID, 'a2pPrimaryCategoryName');
					delete_post_meta($post->ID, 'a2pQuantitySold');
					delete_post_meta($post->ID, 'a2pReserveMet');
					delete_post_meta($post->ID, 'a2pSeller');
					delete_post_meta($post->ID, 'a2pStartTime');
					//it's expired so we're going to delete it... but first it's attachments
					
					$attachments = $wpdb->get_results("SELECT ID FROM " . $wpdb->prefix . "posts WHERE post_type='attachment' AND post_parent='" . $post->ID . "'");
					
					//for each attachment - delete
					foreach ($attachments as $attachment) {
						wp_delete_attachment($attachment->ID);
					}
					wp_delete_post($post->ID);
				}
			}
			
	}

First question, did you develop this “ad framework” for wordpress or are you using a third party plugin? The reason I ask, is the way they are doing deletes is horrid. There are a lot of ways this can be improved upon but I’ll need a bit more information.

First off, what does a2p_is_expired_post do? I’m assuming it is looking at meta data and determining if the end date is before today’s date (thus the ad isn’t being shown anymore), is that correct? Regardless can you indicate which table that data is stored in and how it relates to the posts table?

A quick fix to this would be only returning posts you KNOW to be expired, that will greatly reduce the number of posts you loop through (and their attachments). Can you give us an idea how often ads expire and how many are usually deleted when this process is run? Are we talking 50 ads, 250, 1000?

Depending on how many you need to be able to delete per run, a quick fix might not be the solution, and you may need to refine the innards of your foreach loops too (but we can tackle that after we solve the initial problem of returning what seems to be TOO much data to be processed)

a2p_is_expired_post = i don’t know i think its intended to run that function to delete. usually there are around 50 ads at a time that need to be deleted.

Showing rows 0 - 29 (120021 total, Query took 0.0018 sec)
SQL query:
SELECT *
FROM wp_auction2post_auctions this is from phpmyadmin

Showing rows 0 - 29 (12469 total, Query took 0.0007 sec)
SQL query:
SELECT *
FROM wp_posts
LIMIT 0 , 30

Hi miniz98, welcome to the forums,

Do you really need to SELECT everything?
Even if you do, you should see some improvement in db performance if you specify the field names individually.

I don’t know what to do. Can we negotiate money to get this fixed?

If you have the free version of the auction2post plugin the author will probably ask you to get the premium version, but ask if he’s fixed the memory problem first.

If you have the premium version, then support should be included, no? Did you try its support forum?

I fixed it. Thanks for all the help. You can close the thread.