Is it possible to use a default partition for all reads?

Question

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?

Answer 1

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 🤔

Answer 2

Consider adding is_deleted as the first column in many of the INDEXes. Then, whenever the WHERE clause tests that column, (with =), you will get the desired performance.

Caveat: There are lots of subtle things that might get messed up. Please provide SHOW CREATE TABLE and the main SELECTs and UPDATEs so I can advise further.

More

Plan A: All(?) queries filter on is_deleted explicitly by having it in the WHERE clause. And the INDEX(s) assist with performance.

Plan B: "Deleted" rows are removed from the table, possibly moving them to another table. This requires extra code to explicitly "move" the rows from one table to another.

Plan C: PARTITION the table into two partitions based on is_deleted. This combines both the best and worst of both Plans A and B. I do not recommend it; it is clumsy and still requires code changes. Changing the value of is_deleted "automatically" deletes the row from one partition and adds it to the other. But the WHERE clause changes are still necessary. Or you could explicitly specify the partition -- either way there are changes to all(?) SELECTs. Also UNIQUE and FOREIGN KEY have limitations due to PARTITION.