Should MEDIUMINT be avoided in MySQL?

Question

I came across a comment on the following blogpost that recommends against using MEDIUMINT:

Don’t use [the 24bit INT], even in MySQL. It’s dumb, and it’s slow, and the code that implements it is a crawling horror.

4294967295 and MySQL INT(20) Syntax Blows

An answer on Stack Overflow also notes that SQL Server, PostgreSQL and DB2 don't support MEDIUMINT:

What is the difference between tinyint, smallint, mediumint, bigint and int in MySQL?

Should MEDIUMINT be avoided or should I continue to use it in the cases where it best represents the data I am storing?

Answer 1

InnoDB stores MEDIUMINT as three bytes value. But when MySQL has to do any computation the three bytes MEDIUMINT is converted into eight bytes unsigned long int(I assume nobody runs MySQL on 32 bits nowadays).

There are pros and cons, but you understand that "It’s dumb, and it’s slow, and the code that implements it is a crawling horror" reasoning is not technical, right?

I would say MEDIUMINT makes sense when data size on disk is critical. I.e. when a table has so many records that even one byte difference (4 bytes INT vs 3 bytes MEDIUMINT) means a lot. It's rather a rare case, but possible.

mach_read_from_3 and mach_read_from_4 - primitives that InnoDB uses to read numbers from InnoDB records are similar. They both return ulint. I bet you won't notice a difference on any workload.

Just take a look at the code:

ulint
mach_read_from_3(
/*=============*/
        const byte*     b)      /*!< in: pointer to 3 bytes */
{
        ut_ad(b);
        return( ((ulint)(b[0]) << 16)
                | ((ulint)(b[1]) << 8)
                | (ulint)(b[2])
                );
}

Do you think it's much slower than this?

ulint
mach_read_from_4(
/*=============*/
        const byte*     b)      /*!< in: pointer to four bytes */
{
        ut_ad(b);
        return( ((ulint)(b[0]) << 24)
                | ((ulint)(b[1]) << 16)
                | ((ulint)(b[2]) << 8)
                | (ulint)(b[3])
                );
}