4

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?

3
  • Provide a sample - CREATE TABLE, including all indices, and a query. Can I omit id from the CREATE INDEX statement and still enjoy retrieving data via the index in order by all 4 columns? In general - no.
    – Akina
    Commented Nov 1, 2023 at 4:35
  • 3 columns in the index doesn't imply the remaining id key is in order in that index. Note its secondary composite indexes that have a PK reference appended as the PK is a clustered index, and secondary ones aren't. Note a primary key can be composite too. Creating a sample fiddle with your query, and explain {query} will show index usage.
    – danblack
    Commented Nov 1, 2023 at 5:53
  • @danblack - I've never seen that happen, but that does not prove anything. Have you looked at the code to see that it might actually scramble them? I would expect creating a "temp" table of a,b,c,id, then sorting, then constructing the index BTree would be faster than sorting a,b,c, then tacking on id somehow, and finally constructing the BTree.
    – Rick James
    Commented Nov 10, 2023 at 0:33

3 Answers 3

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);
1
  • But, who's to say that pd-dkc's suggestion is not unnecessary?
    – Rick James
    Commented Nov 10, 2023 at 0:36
1

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.