Re: Help with partially unique index

[Rick James]

Given that this trio is unique: UNIQUE KEY `udx_acc_user_cat_stat_comp` (`account_id`,`user_id`,`category_id`), the following will help the other queries:

INDEX(account_id, user_id, status),
INDEX(account_id, status)

PARTITIONing by account_id would _not_ help. This is because account_id is already the first column in these indexes. Also 2048 partitions is a lot; some operations open all partitions, even if they don't need to; this is costly.

Get rid of the NULL account_ids, then promote that trio to PRIMARY KEY. That will help performance significantly.

By having PRIMARY KEY(`account_id`,...), all the rows for a given account_id are 'near' each other. This cuts down on I/O. And I/O is the biggest performance issue in large tables. Simply UNIQUE will not suffice -- because of the NULLs.

That is, having the PRIMARY KEY start with account_id is better than partitioning on account_id.