Is it possible to use a default partition for all reads? I have a legacy application which queries one of its database (MySQL on AWS Aurora) tables extensively. Lots of queries and lots of query types. 90% of the rows in this table are outdated and are marked is_deleted. I want to try and make the performance better so I thought of partitioning the table by is_deleted and making all queries go to the partition where is_deleted=false. The problem is that there are many hundreds, if not thousands, of queries I'd need to rewrite in order to insert is_deleted to the where clause of every query.
Is there a way to define the is_deleted = false partition as the default for all reads? If not is there some other way to do this?
If there are no situations where you need to query soft-deleted records, a simpler mechanism would be to have a table just for deleted records. You could then implement a scheduled job to copy all is_deleted = true records to the historical table and remove the originals from the source table.
Doing this would ensure the primary table contained all of the active records and no application queries would need to be updated.
If writes don’t happen very often, you could even look at using an AFTER UPDATE trigger to automatically copy (and maybe remove) soft-deleted records from the table as they’re invalidated.
This, I believe, would be much simpler to implement if going through all of the application SQL queries is untenable