Do you need to explicitly include the primary key in a composite index?

Question

I recently read that when creating a composite index in MariaDB that the primary key is appended to the end of the index "under the covers". I access my data many times in order by these four columns: a, b, c, and id (id being the primary key). Can I omit id from the CREATE INDEX statement and still enjoy retrieving data via the index in order by all 4 columns? Or should I still explicitly include id in the CREATE INDEX definition?

Answer 1

Yes you can definitely skip adding id to the CREATE INDEX

Let's say you already created that index with (a,b,c,id)

Here is how you can show that you can do with the id column:

Run pt-duplicate-key-checker against your MySQL Instance.

Let's say you ran this

CREATE DATABASE IF EXISTS mydb;
USE mydb
CREATE TABLE mytable
(
    id INT NOT NULL AUTO_INCREMENT,
    a CHAR(10),
    b VARCHAR(20),
    c TINYINT
    PRIMARY KEY (id)
    KEY abc_id_ndx (a,b,c,id)
);
INSERT INTO mytable (a,b,c) VALUES ('CHAR1','VARCHAR1',1);
INSERT INTO mytable (a,b,c) VALUES ('CHAR2','VARCHAR2',2);
INSERT INTO mytable (a,b,c) VALUES ('CHAR3','VARCHAR3',3);

When you run pt-duplicate-key-checker, it will report the SQL to change that index. In fact, the recommended SQL will be something like:

ALTER TABLE mytable DROP INDEX abc_id_ndx, ADD INDEX abc_id_ndx (a,b,c);

Answer 2

MariaDB might do this but thinking more generically ...

Any secondary index has to have a "pointer" to the data record that it refers to. If MariaDB uses the PK for that purpose, fine. Other DBMSs do it differently (yes; I'm thinking of Oracle, here).

If you want to look something up by its [unique] Primary Key, then you don't need anything else to find it. Just the Primary Key value will do and Primary Keys are always indexed.

If you want to look something up by any other value(s), then they need to be indexed. If you regularly use multiple columns to do those lookups, then create a composite index on those columns (it's common for a database engine to "pick" only one index for each query).

So no; you don't need to include the Primary Key in any composite, secondary indexes.

Answer 3

I like to include the PK when I depend on id being part of the INDEX; eg, to make it "covering". This is to clue the human reader in that I need it. (Suppose they convert to Oracle -- The efficiency of being "covering" would get lost.)

Bottom Line -- Add id if you need it. Else, don't worry.