I am looking for a way to optimize large table with 8M+ rows.
Table struct looks like follow: id INT PRIMARY AUTOINCREMENT removed TINYINT(1) status TINYINT(1) ~10 other data fields
There is also (id,removed,status) unique index
About half rows have removed=1 and half rows have removed=0 90% rows have status=1, remaining 10% have values with range 0-9
Application uses about 200 different queries accessing this table directly or with JOINs. Rewriting application and queries is out of my scope.
About 90% queries are accessing only removed=0 AND status=1 rows (WHERE...removed=0 AND status=1...), ~1% queries are accessing both removed and unremoved rows regardless of status, and 9% queries are direct PK hit (where id=X).
The question is - will partitioning by removed field (0,1) speed up these 90% of queries with removed=0? If innodb engine will have to access only ~3.5M instead of 7M rows, it theoretically should speed up all queries? Or this is not the case?