Mysql – With MyISAM is there any index size savings when using INT vs BIGINT

myisamMySQL

There is obviously a four byte per row space savings in a data column when using INT vs a BIGINT type.

But I have been trying to figure out if there is also any space savings for myisam indexes on integers since they are typically unpacked.

MySQL doesn't say what the structure type is for indexes – it only calls the units "nodes" and then doesn't mention their actual size.

So given the same set of numbers, say a million rows of integers, does using INT instead of BIGINT reduce the node size? Or because there can still be an unlimited number of rows despite the integer range, does the node size always have to be rather large?

I guess I could whip up a demo to test this question but maybe someone already understands this properly on a technical level and can explain it.

Best Answer

Decided this was easy enough to investigate myself, even though I still do not understand the underlying specifics of why (feel free to elaborate?)

The answer is: YES int most definitely creates smaller indexes than BIGINT

I made two tables, first with four unsigned INT columns, second with four unsigned BIGINT

I made a compound index across all four columns for each table.

Then I added a million rows of random unsigned smallints 0-65535 to each table.

(each table has identical data, both numbers and row order)

Then I optimized and flushed both tables just to be certain.

INT

Data    17,000  KiB
Index   31,610  KiB
Total   48,610  KiB

BIGINT

Data    33,000  KiB
Index   56,921  KiB
Total   89,921  KiB

17,408,000 int.MYD
32,368,640 int.MYI

33,792,000 bigint.MYD
58,287,104 bigint.MYI

added:

I was concerned the random data repeated (I found some cases).

So I added a primary column with auto-increment to each table and emptied them. Then I filled each with the numbers from 1 to 1,000,000 in each column, incrementing for each row sequentially.

INT

Data    20,508  KiB
Index   32,301  KiB
Total   52,809  KiB

BIGINT

Data    40,039  KiB
Index   54,178  KiB
Total   94,217  KiB

So not quite 50% savings but definitely adds up, even for index storage.

Related Question