Re: Help with partially unique index
Those 3 columns probably should be the PRIMARY KEY. Then, a non-unique "secondary" index of (status, score) could exist.
Note: When you have PRIMARY KEY(a,b,c), INDEX(d,e), the latter is actually implemented like INDEX(d,e,a,b,c). This leads to any query needing _only_ d,e,a,b,c would be "covered" by that index and not need to touch the data, only the index.
Examples:
SELECT any-columns WHERE a=1 AND b=2 AND c=3
is very happy to use the primary key and very efficient. It will return at most 1 row. Keep in mind that the PRIMARY KEY lives with the data -- all columns are stored together, and it is sorted in PK order.
SELECT a,b,c WHERE d=9 AND e=8
is very happy to use the only secondary key and very efficient. It may return many rows, but they will be "clustered" together in the INDEX's BTree.
There is no way (at least no in MySQL) to say (a,b,c <- unique, plus -> d,e).
You mentioned partitioning. Please elaborate, because indexing 'rules' change when there is PARTITIONing. And _most_ forms of partitioning are _useless_ for performance. Please show us the actual `CREATE TABLE` and the key `SELECTs`. (By simplifying your schema, I am likely to be answering the wrong question!)
Can `account_id` really be NULL? If so, that adds some wrinkles that need discussing.