I have a MySQL database that runs on a dedicated server running windows on a 6-core CPU with 8GB ram.
I have a few scheduled jobs running which run complicated views and output the results to a table (which is queried by the website to avoid duplication of complicated calculations) - we'll call this tblMainTable
The scheduled jobs first delete any records that have been previously inserted of the same type and then inserts new records.
But now we have heavy traffic on the tblMainTable (i.e more records and lots of inserts and updates constantly) the delete is taking ages - sometimes over 5 minutes!
There is approx 50,000 records in tblMainTable which are being updated constantly
The following statement is being used for the delete
DELETE FROM tblMainTable WHERE subTypeId = 2 AND typeId = 1;
Approx 29,000 records in tblMainTable meet the criteria above
I am now a bit lost, I've tried all I can think of and it seems the DELETE just takes ages and is causing a real problem. I assume it is because the table is being changed so frequently but there must be a way to make this quicker.
Any help greatly appreciated!