memory alignment in MySQL

[Joy]

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.

[Jeff North]

On 30 Jul 2004 19:26:49 -0700, in mailing.databas e.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.