How to declare secondary or non-unique index in mysql?

Question

It seems like mysql only provides four kinds of indexes, which are primary, unique, key, fulltext and spatial. But I was wondering how to declare index for those of columns which are not unique but often gets indexed? Because my database server is hitting a bottleneck, and I'd like to create index for columns that are frequently indexed.

Any help would be appreciated. Thanks in advance.

Solution

Please distinguish between "key" and "index".

The former is a logical concept (restricts, and therefore changes the meaning of data) and the later is physical (doesn't change the meaning, but can change the performance)¹.

Let's get the basic concepts straight:

• A "superkey" is any set of attributes that, taken together, are unique.
• A "candidate key" (or just "key") is minimal superkey - if you take any attribute away from it, it is no longer unique.
• All keys are logically equivalent, but we pick one of them as "primary key" for practical² and historical reasons, the rest are called "alternate keys".
• In the database, you declare primary key using PRIMARY KEY constraint, and alternate key using UNIQUE constraint on NOT NULL fields.
• Most DBMSes (MySQL is no exception) will automatically create indexes underneath keys. Nonetheless, they are still separate concepts and some DBMSes will actually allow you to have a key without index.

Unfortunately, MySQL has royally messed-up the terminology:

• MySQL uses column constraint KEY as a synonym for PRIMARY KEY.
• MySQL uses table constraint KEY for index, not key, same if you were to use CREATE INDEX statement.

So for example, both...

CREATE TABLE T (
  A INT PRIMARY KEY,
  B INT
);

CREATE INDEX T_IE1 ON T (B);

...and...

CREATE TABLE T (
  A INT KEY,
  B INT,
  KEY (B)
);

...mean the same thing: primary key on A (with unique/clustering index on A) and non-unique index on B.

---

¹ Unique index is an oddball here - it straddles both worlds a little bit. But let's leave that discussion for another time...

² For example, InnoDB clusters the table on primary key.