I have some kinda big tables to tune and one thing I'm trying to is to downsize the data length of a row in a table, I have some tables which it's like this
# create table a ( i bigint(20) unsigned not null );
# show columns from a;
# insert into a values (12345);
it shows .. "bigint(20) unsigned" in the type and shows 9 in the data_lenght of a "SELECT * FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'a';" all right till there for the number '12345' should be stored in less them 20 bites, but if I do ..
# alter table a modify i int(4) unsigned null;
# SELECT * FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'a';
It trims 7 bytes instead 4 (as I understood it should do from
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html ) and though i thought it would not get worst if I do ...
# alter table a modify i int unsigned null;
it shows .. "int(10) unsigned" in the type and keep showing 7 bytes in the data_lenght of a "SELECT * FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'a';"
I thought when you pass a parameter to the type, no matter which type it says to MySQL that he should use at most that quantity of bytes to store the number ... but it seams not work this way in numeric types ... but I search out the documentation and can't find where to explain me this ... can someone tell me why or point me some directions to understand how it works and why it works this way?
Thanks.
--
/**
* @analist Ricardo Striquer (ricardophp yahoocombr)
* @var Técnico em PDD
* @see
http://www.programabrasil.org
* @see
http://twitter.com/rstriquer
*/