2

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

Jimmie Berg
  • 2,212
  • 1
  • 13
  • 7
  • 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 Apr 18 '15 at 06:55
  • Thank you, I have added that info to the OP – Jimmie Berg Apr 20 '15 at 07:16

1 Answers1

6

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I understand. The reason as to why I was trying to determine the Index length was to get an idea of how much the index would grow in size along with my table. Thank you very much for the answer! – Jimmie Berg Apr 21 '15 at 14:03