I have a website that auto generates odds data to mysql table via cron job every 2hrs. Over time the data in my table is exceeding the limit set from my hosting of 100mb.

I want to stop my table from overloading and to do this, I want it to delete previous odds data and only show the latest odds. The odds data is in the wp_postmeta table.

Here is a sample of data in one row:

• meta_id 3141189
• post_id 20583
• meta_key Odd
• meta_value {"FixtureMatch_Id":"392665","Bookmaker":"18BET","UpdatedDate":"2018-12-14T20:57:46.98","Type":"Asian Handicap","HomeOdds":"1.49","AwayOdds":"2.71","Handicap":"2"}

So I have lots of these rows, and I want to know how I can use PHP to remove old odds, lets say odds that are before 5 days are deleted, how would I do this?

I have tried to do this, but heard it doesn't work like that.

global $wpdb; // Global object database variable$updatedDate = [];

//set how many days odds will show until.. currently set to -5 days.
$endDatefind = (new \DateTime('now'))->modify("-5 day")->format('Y-m-d'); // creating datetime object //finds all dates from 2000 to 5 days before todays date$findquery = $wpdb->get_results ( " SELECT * FROM wp_postmeta WHEREmeta_key = 'Odd' AND STR_TO_DATE(meta_value, '%Y-%m-%d') >= '2000-01-01' AND STR_TO_DATE(meta_value, '%Y-%m-%d') =< '$endDatefind'

");


Anyone know how I can do it?

• 100mb is a very low limit, who are you hosting with? The solution here may be to just move hosts, it would be much easier than trying to cram ever larger amounts of data into a 100mb limit ( which to be honest, you won't manage unless you can take your existing 100mb database, and downsize it to less than 5-10mb, else you'll be in exactly the same situation in a week or months time ). Also, what post type is your post? Keep in mind that post meta queries are expensive too. Do you have access to shell/WP CLI? – Tom J Nowell Dec 15 '18 at 0:47