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
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
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.