MySQL: Why is my index length 0.0 bytes?

Question

I have a simple table containing 4 columns.

Column A (int), Column B (int), Column C (int), Column D (int)

I have constructed an index on (Column B, Column A, Column C), which works well performance wise. My table contains 5 million rows, and using the index to select desired rows works instantly (<0.00 s).

While inspecting the table, however, I see that my Index length is 0.0 bytes. Why? How can my index take no memory at all?

Info:

SHOW CREATE TABLE kpi_store_hour

CREATE TABLE kpi_store_hour ( kpiID int(11) NOT NULL, companyID int(11) NOT NULL, timestamp int(11) NOT NULL, value float NOT NULL, PRIMARY KEY (kpiID,companyID,timestamp) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

SHOW TABLE STATUS

Name: kpi_store_hour

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 4973952

Avg_row_length: 95

Data_length: 476037120

Max_data_length: 0

Index_length: 0

Data_free: 6291456

Auto_increment: NULL

Create_time: 2015-03-04 11:14:06

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

SELECT * FROM kpi.kpi_store_hour WHERE kpiID = 0 AND companyID = 1 AND timestamp < 1353792707;

Duration/fetch: 0.000 sec / 0.000 sec

EXPLAIN SELECT * FROM kpi.kpi_store_hour WHERE kpiID = 0 AND companyID = 1 AND timestamp < 1353792707;

id: 1

select_type: SIMPLE

table: kpi_store_hour

type: range

possible_keys: PRIMARY

key: PRIMARY

key_len: 12

ref: NULL

rows: 743

Extra: Using where

Please provide SHOW CREATE TABLE, SHOW TABLE STATUS, the SELECT, and EXPLAIN SELECT .... There are many possible explanations; those bits of info should make it easy to give you an answer. — Rick James

Rick James Rick James · Accepted Answer · 2015-04-20T22:56:21

In InnoDB, the PRIMARY KEY is "clustered" with the data. Phrased differently, the data is stored in a BTree that is ordered by the PK. Since the two (data and PK) co-exist, their size is counted in Data_length (476MB) and nothing in Index_length. If you had any 'secondary' keys, they would be counted in Index_length.

The table has 4 4-byte fields, so theoretically a row should occupy only 16 bytes. Note that Avg_row_length is 95. This is because of

Overhead for each column
Overhead for each row
Overhead for BTree
and some overhead for the PRIMARY KEY.

key_len: 12 -- That implies that the 3 4-byte fields in the PK were used...

WHERE kpiID = 0 AND companyID = 1 AND timestamp < 1353792707 could quickly drill down in the BTree to the first row with kpiID = 0 AND companyID = 1, then scan until timestamp < 1353792707 fails. And it estimated that 743 rows would be encountered.

MySQL: Why is my index length 0.0 bytes?

1 Answers