Comparing the performance of int, char, and varchar in MySQL

There are many seemingly true "rumors" on the Internet. Of course, they are not malicious. Most of them are because developers are unwilling to take the initiative to research themselves, and instead believe what others say.

There are also many rumors about databases, such as "int performance is much higher than char".

I recently conducted a performance test on int, long, char, and varchar, and found that there is actually not much performance difference between them:

Note: c8=char(8), s8=varchar(8), i8=(bigint), c4=char(4), s4=varchar(4), i4=char(4)

Query 1 million rows without index:

Execute [c8 query] 20 times, average time consumption 312.0ms
Execute [s8 query] 20 times, average time consumption 334.3ms
Execute [i8 query] 20 times, average time consumption 276.95ms
Execute [c4 query] 20 times, average time consumption 354.95ms
Execute [s4 query] 20 times, average time consumption 340.45ms
Execute [i4 query] 20 times, average time taken is 291.1ms

Create an index:

c8 indexing took 2439ms
s8 indexing took 2442ms
i8 indexing took 1645ms
c4 indexing took 2296ms
s4 indexing took 2303ms
i4 indexing took 1403ms

Query with index:

Execute [c8 query] 10000 times, average time consumption 0.271ms
Execute [s8 query] 10000 times, average time consumption 0.2354ms
Execute [i8 query] 10000 times, average time consumption is 0.2189ms
Execute [c4 query] 10000 times, average time consumption 0.303ms
Execute [s4 query] 10000 times, average time consumption 0.3094ms
Execute [i4 query] 10000 times, average time taken is 0.25ms

in conclusion:

No index: Full table scan does not become faster because the data is smaller, but the overall speed is the same, int/bigint as native types is slightly faster by 12%.

With index: char and varchar have similar performance, int is slightly faster by 18%

In terms of data storage, reading and writing, integers are the same as strings of equal length, but varchar has an extra byte so performance may be slightly affected (1/n).

In terms of data operations and comparisons, integers benefit from native support and are therefore slightly faster than strings.

If indexes are used, the performance difference between integers and strings is even smaller.

In actual development, many developers often use strings such as char(1) and char(4) to represent type enumerations. In my opinion, this approach is the best solution because it is far superior to data types such as int and enum in terms of storage space, computing performance, readability, maintainability, and scalability.