CODEWITHSUNDEEP
mysqlindexingprimary-keyinnodb
Frantisek
Frantisek

Reputation: 7703

If MySQL's InnoDB PRIMARY columns are automatically indexed, why is the index length reported as zero?

This is my MySQL InnoDB database. Notice how the table days reports the index length as 0.

enter image description here

This is the table data, scheme and indexes:

enter image description here

As you can see, the table has a PRIMARY column defined. This should be indexed, right? So there should be a certain index length.

However, when I actually add the INDEX column, such as this:

enter image description here

The table suddenly reports a certain index length, as can be seen here:

enter image description here

So I wonder: what is going on? What exactly is the index length and why does it not take PRIMARY keys into account, if they are indexed?

Upvotes: 2

Views: 118

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562801

InnoDB stores the table rows in a clustered index based on the primary key. So, the data_length shows the size of pages occupied by the primary key.

index_length shows the size of pages occupied by secondary (non-primary) indexes.

Re your comment:

Yes, it's unnecessary in this table to create an extra index on the first column of your primary key. MySQL doesn't prevent you from creating such a superfluous index because it trusts you know what you're doing. :-)

You can use a tool like pt-duplicate-key-checker to analyze your metadata for duplicate indexes. In one such case I found 400GB of wasted space due to duplicate indexes!

Upvotes: 2

Related Questions