Very large INNODB table file

[UserName]

One of the tables in my database has grown to a very large size, and I'm trying to determine why. innodb_file_per_table is enabled in my.cnf, so there are individual .ibd files for each table.

The table in question has a ibd of 40gb, but I'm unable to account for 10 gb of data. The table structure is as follows:

p_id --- int(10)
a_id --- int(5) [max value 72]
e_id --- tinyint(2) [max value 10]


Only one index exists, on p_id (cardinality of 1,326,888). According to the table status, I'm using 10gb in index and ~20gb in data. The total usage is 30gb.

Where are the other 10gb coming from?

Also, procedure_analyze suggests converting p_id to a MEDIUM, and a_id and e_id to ENUM. Will this result in substantial disk space savings?

Any suggestions or pointers please......

Thanx

[Ronaldo]

Originally Posted by UserName

Also, procedure_analyze suggests converting p_id to a MEDIUM, and a_id and e_id to ENUM. Will this result in substantial disk space savings?

Thanx

it might, but not enough, in my opinion, to compensate for the evil introduced into your application by using ENUM

a_id should be TINYINT like e_id, i.e. just 1 byte

the number in parentheses behind TINYINT does not mean what you think it does .

Regards,

Ronaldo

[UserName]

Changing from TINYINT(2) to TINYINT(1) made a slight change in file size on my test database. I'm waiting for the system to finish altering the a_id field now. This operation usually takes an hour or so.. will reply back with my findings.

[Ronaldo]

Originally Posted by UserName

Changing from TINYINT(2) to TINYINT(1) made a slight change in file size on my test database.

I doubt that very much

TINYINT(2) is the same as TINYINT(1) is the same as TINYINT(937)

like i said, the number in parentheses doesn't do what you think it does.

Regards,

Ronaldo

[UserName]

Initial results show that the ibd file decreased from 40gb to 23gb. Doing a little testing to make sure the system works ok.

[UserName]

Originally Posted by Ronaldo

I doubt that very much

TINYINT(2) is the same as TINYINT(1) is the same as TINYINT(937)

like i said, the number in parentheses doesn't do what you think it does.

I read on mysql that the parenthesis number has to do with what's shown in display. TINYINT can hold values up to 127. So that's why it's oky for a_id and e_id.

The biggest change was going from INT to MEDIUMINT on p_id. At least I think that's where my disk savings came from.

[Ronaldo]

Originally Posted by UserName

The biggest change was going from INT to MEDIUMINT on p_id. At least I think that's where my disk savings came from.

yup

that's because INTEGER is 4 bytes and MEDIUMINT is only 3