Use of PRIMARY KEY lowers innodb file size?

Question

Im a bit confused and need some input.

I have created 2 tables here

CREATE TABLE `table1` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Value` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `table2` (
  `Value` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When i look at the size of the file in the temp folder. They are both 96 kb

enter image description here

I simply inserted 10240 items into both columns.

SELECT count(*) FROM temp.table1;
SELECT count(*) FROM temp.table2;

Gives

10240
10240

If i now look into the size of the tables, this is what i get.

enter code here

I discovered this when i changed one of my innodb tables and added a AUTO_INCREMENT PRIMARY KEY, and the size was over 3 GB less.

I am under the impression that innodb has a internal PRIMARY KEY in case one isnt defined? But how can it be that the table with 1 column less, actually takes up more space? There is no index in the table2 (except the internal PRIMARY KEY) , and a PRIMARY KEY defined on table1

Does this mean that the internal innodb PK is using BIGINT for instance?

EDIT

After changing the PK to BIGINT , the sizes of the files are now the same. Simple as that.

This does however raise another question. Is the LIMIT of MySQL rows the maximum of BIGINT (which is big yes, but there is in fact a limit then right)?

What happens if you drop the key again? Does it get bigger again or does it remain small? MySQL basically recreates the whole table for a change like this, so it might reorganise the data in the process, causing the file to shrink. — GolezTrol, Nov 21 '13 at 16:28
By the way, 3GB less on a file of 400KB? Did you mean '30KB less'? — GolezTrol, Nov 21 '13 at 16:29
The innodb internal PK is of type BIGINT, thats why the size was different. But this create another question for me regarding LIMIT to MySQL — Mad Dog Tannen, Nov 21 '13 at 16:29
@GolezTrol no this was just as example. But i changed a live table today without a PK and it shrank by 3G. (It was 25 G before, after 22G) — Mad Dog Tannen, Nov 21 '13 at 16:30
possible duplicate of [Maximum number of records in a MySQL database table](http://stackoverflow.com/questions/2716232/maximum-number-of-records-in-a-mysql-database-table) — GolezTrol, Nov 21 '13 at 16:32
Anyway, with your first answer answered, your second question is a duplicate of [this question](http://stackoverflow.com/questions/2716232/maximum-number-of-records-in-a-mysql-database-table). Basically, the number of rows is not limited BIGINT, unless you make a BIGINT the primary key, but you could choose to make a column of a different type the primary key allowing you even bigger numbers. But nevertheless, they will probably have invented a BIGGERINT before hard disk will be this large. — GolezTrol, Nov 21 '13 at 16:34
Well then, if you dont have a PK defined, your limit is BIGINT. Innodb make use of PK even if you dont define one. — Mad Dog Tannen, Nov 21 '13 at 16:36
But if you *do* define a PK, you can make it a VARCHAR(200) and have way more values. By the way, where did you find that InnoDB creates an internal id of BIGINT? Did you conclude that based on just the file size? — GolezTrol, Nov 21 '13 at 22:30
@GolezTrol , I know, thats what I said ;) Anyway, check out the section 'Clustered and Secondary Indexes'. http://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html , it doesnt say that it is of type BIGINT, but it was the only type (ok i didnt try it all) where the size of the files would be equal as they should. But if a value is monotonically increased it must be of type INT,BIGINT for instance. What else could it be? Please correct me if i am wrong. — Mad Dog Tannen, Nov 22 '13 at 08:23

score 1 · Answer 1 · answered Nov 22 '13 at 00:21

Maybe this answer is not what you want. But If you are looking for how to reduce InnoDB data file size, Barracuda format can help you. (if you already know about it, I will delete this answer). Barracuda format compresses InnoDB data file

Let's see how to use it. first check current innodb_file_format:

mysql> show variables like 'innodb_file_format';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+

Default value of innodb_file_format is Antelope, if you want to use Barracuda, add innodb_file_format = barracuda to my.cnf and restart. That's all.

Now, Barracuda is enabled so when you create table, you can specify KYE_BLOCK_SIZE (default value is 16KB). If you set to 8KB, Table size recude to 1/2 compared to 16KB. if 4KB or 2KB, 1/4 and 1/8 respectively.

CREATE TABLE temp(...)
ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

But, KEY_BLOCK_SIZE=8 is recommended. 4 or 2 is good for file size, but performance is bad. but 8 reduces by 50% without performance loss.

Thanks for your input. At this moment diskspace is not an issue still got 4 T. But this is to keep the size of the files down correct? Do you know if it affect the size of the indexes also? — Mad Dog Tannen, Nov 22 '13 at 08:29
@KayNelson Yes, reduces both index and data. actually Barracuda reduces InnoDB Page. and InnoDB Page has both index and data. (not like MyISAM's MYD, MYI) — Jason Heo, Nov 22 '13 at 08:40
Jungsu thanks alot for your input. I will immediatley dive into this and learn more about baracuda. Im running Antilope atm. THanks again! — Mad Dog Tannen, Nov 22 '13 at 08:41
@KayNelson I am looking for you result. plz Let me know your final result. thanks. In my case, ibd file size is reduced about 1/2. and Barracuda is using in production. — Jason Heo, Nov 22 '13 at 08:45

Use of PRIMARY KEY lowers innodb file size?

1 Answers1