MySQL Forums
Forum List  »  Optimizer & Parser

Re: Help with partially unique index
Posted by: Rick James
Date: July 04, 2016 12:50PM

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.

Options: ReplyQuote

Written By
June 22, 2016 04:44AM
July 02, 2016 03:33PM
Re: Help with partially unique index
July 04, 2016 12:50PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.