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.
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)1.
Let's get the basic concepts straight:
Unfortunately, MySQL has royally messed-up the terminology:
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.
1 Unique index is an oddball here - it straddles both worlds a little bit. But let's leave that discussion for another time...
2 For example, InnoDB clusters the table on primary key.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With