I am having issues with MySQL buckeling after a million rows. While I have never taken mysql to this extent before, I would hope it wouldn't have issues with this. a million rows should be nothing for a RDMS. This is affecting Deletes, selects, and updates.
Does anyone know why it would behave like this?
I have indexes on all relevant columns
PK - Auto Increment (record_id)
Indexes on 3 other columns (account_id, site_id, status);
I also have a unique compound across 3 columns (site_id, url, status) (I ran into a situation of which required mass inserts using INSERT IGNORE.. and this provided the best way to let the database kick out rather then me making 2 connections to check for existance first then insert if not exists)
I guess out of this... if I have a compound unique should I remove the indexes that already exist as part of the unique?