MySQL Reducing Storage Space

Question

I have a table definition:

CREATE TABLE `k_timestamps` ( `id` bigint(20) NOT NULL, `k_timestamp` datetime NULL DEFAULT NULL, `data1` smallint(6) NOT NULL, KEY `k_timestamp_key` (`k_timestamp`,`id`) USING BTREE, CONSTRAINT `k_time_fk` FOREIGN KEY (`id`) REFERENCES `data` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Basically, I have a whole lot of id and data1 key-value pairs, and every few hours I either add new key-value pairs not seen before to the list, or the value of a previous id has changed. I want to track what all the values were for every id in time. Thus, the id column can contain duplicate id's and is not the primary key.

Side note, k_time_fk points to another, much smaller table that has common information for a particular id regardless of what the current time is or value it currently holds.

(id, k_timestamp) should be thought of as the (composite) primary key of the table.

For example,

id k_timestamp data1 1597071247 2012-11-15 12:25:47 4 1597355222 2012-11-15 12:25:47 4 1597201376 2012-11-15 12:25:47 4 1597071243 2012-11-15 13:25:47 4 1597071247 2012-11-15 13:25:47 3 1597071249 2012-11-15 13:25:47 3

Anyways, I ran this query:

SELECT concat(table_schema,'.',table_name), concat(round(table_rows/1000000,2),'M') rows, concat(round(data_length/(1024*1024*1024),2),'G') DATA, concat(round(index_length/(1024*1024*1024),2),'G') idx, concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size, round(index_length/data_length,2) idxfrac FROM information_schema.TABLES ORDER BY data_length+index_length DESC LIMIT 20;

To pull space info on my table:

rows Data idx total_size idxfrac 11.25M 0.50G 0.87G 1.36G 1.76

I'm not really sure I understand this, how can the index be taking up so much space? Is there something obvious I did wrong here, or is this normal? I'm looking to try to reduce to footprint of this table if possible. I'm not even really sure what that k_timestamp_key really buys for me, can it be safely deleted?

The k_timestamp_key is important since you are querying by time. — Ifthikhan
– Ifthikhan, Commented Mar 6, 2013 at 16:48
Is disk space really your concern? 2GB of disk space is "too cheap to measure" in most cases... — Neville Kuyt
– Neville Kuyt, Commented Mar 6, 2013 at 17:00
@NevilleK Looking to see if there's something obvious I can do, as my current web host only allows 2GB max for a DB size. — user17753
– user17753, Commented Mar 6, 2013 at 17:52

innvo · Accepted Answer · 2013-03-06 16:55:18Z

The index is bigger because InnoDB tables will assign a 6 byte primary key when you have no unique column that it can treat as a unique index. All other indexes in the table also contain the primary key... see 14.2.3.12.2. Clustered and Secondary Indexes from the manual

Neville Kuyt · Accepted Answer · 2013-03-07 13:05:06Z

Firstly, yes, this is pretty normal behaviour, as innvo writes.

Secondly, you can optimize the table and its index using OPTIMIZE TABLE. As your primary key is likely to be "fragmented" - i.e. it's not safe to assume that an inserted row is physically next to the previous row - there may be some gains there.

Finally, you may not need a primary key on the table, but you almost certainly need an index if you're querying across millions of rows...

Collectives™ on Stack Overflow

MySQL Reducing Storage Space

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related