MySQL Forums
Forum List  »  Newbie

Re: making a row unique
Posted by: Rick James
Date: February 24, 2009 11:45AM

Review of some INDEX rules that I think both of you are confused about...
* 0-1 PRIMARY KEY on a table.
* Any number of UNIQUE keys on a table.
* PRIMARY KEY implies UNIQUE.
* PRIMARY KEY (a,b) does not imply that 'a' is unique. If it is, you probably want PRIMARY KEY (a) plus INDEX (a,b).
* "UNIQUE KEY" == "UNIQUE".
* "KEY" == "INDEX".

And some guidelines:
* If you have INDEX(a,b), then it "covers" INDEX(a), and you can probably do without the latter.
* Prefixing is usually not useful: INDEX(a(10))
* Prefixing with UNIQUE is usually wrong: UNIQUE(a(10)) constrains the first 10 chars of 'a' to be UNIQUE.
* An AUTO_INCREMENT field should be the PRIMARY KEY. (Yeah, there are other tricks; this is a guideline.)
* In InnoDB, you should provide a PRIMARY KEY.
* In InnoDB, the PRIMARY KEY should be short if you have secondary keys.

Performance hints:
* More indexes means more work on inserts.
* If all the fields needed for a SELECT are found in a single index, then you get a performance boost. EXPLAIN will indicate this with "Using index".
* INDEX(a,b) is useless if you only say WHERE b=123.
* INDEX(a,b) is not as good as (b,a) when you have a>123 AND b=234. That is, want to start with "=".
* foo LIKE '%abc' will not use INDEX(foo), but this will: foo LIKE 'abc%'.
etc.

Options: ReplyQuote


Subject
Written By
Posted
February 23, 2009 12:12PM
February 23, 2009 12:20PM
February 23, 2009 02:11PM
February 23, 2009 02:41PM
Re: making a row unique
February 24, 2009 11:45AM
February 24, 2009 03:03PM
February 24, 2009 09:37PM
February 23, 2009 12:33PM
February 23, 2009 12:56PM
February 23, 2009 01:00PM
February 23, 2009 01:02PM


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.