On 30 Jul 2004 19:26:49 -0700, in mailing.database.mysql
kp*******@yahoo.com (Joy) wrote:
| CREATE TABLE my table
| (coll_a INT NOT NULL,
| coll_b SMALLINT,
| coll_c SMALLINT,
| INDEX b_index (coll_b)
| );
|
| Above is the table I will created. I declare coll_b and coll_c, which
| together will cose me 4 bytes in one row. Is it? What if I declare
| both of them as INT, will it cost me more memory? How about the
| memory needed for the index b_index? Not sure how memory alignment
| will affect MySQL. Thanks.
It's not about 'memory alignment' but more about using the correct
datatype for your data.
----------------------------------
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
A small integer. The signed range is -32768 to 32767. The unsigned
range is 0 to 65535.
INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295.
----------------------------------
So if your coll_b and coll_c fields will never overflow the maximum
values of smallint then by all means use these datatypes.
As for indices, these assist the database to search for your data.