MySQL Forums
Forum List  »  Optimizer & Parser

Re: Help with partially unique index
Posted by: Rick James
Date: June 25, 2016 10:45PM

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.

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.

Options: ReplyQuote

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

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.