Performance comparison of int, char and varchar in MySQL

The content of this article is about the performance comparison of int, char and varchar in MySQL. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

There are many specious "rumors" on the Internet. Of course, they are not malicious. Most of them are developers who are unwilling to take the initiative to research on their own, but instead believe in other people's words.

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 not much performance gap between them:

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

Query without index for 100w rows:
Execute [c8 query] 20 times, with an average time consumption of 312.0ms
Execute [s8 query] 20 times, with an average time consumption of 334.3ms
Execute [i8 query] 20 times, with an average time consumption of 276.95ms
Execute [c4 query] 20 times, with an average time consumption of 354.95ms
Execute [s4 query] 20 times, with an average time consumption of 340.45ms
Execute [i4 query] 20 times, with an average time consumption of 291.1ms

Create index:
c8 index takes 2439ms
s8 index takes 2442ms
i8 index takes 1645ms
c4 index takes 2296ms
s4 index takes 2296ms 2303ms
i4 index takes 1403ms

Query with index:
Execute [c8 query] 10,000 times, the average time is 0.271ms
Execute [s8 Query] 10,000 times, the average time is 0.2354ms
Execute [i8 query] 10,000 times, the average time is 0.2189ms
Execute [c4 query] 10,000 times, the average time is 0.303ms
Execute [s4 query] 10,000 times, the average time consumption is 0.3094ms
Execute [i4 query] 10,000 times, the average time consumption is 0.25ms

Conclusion:
No index: full table scan will not be performed because The smaller the data, the faster it becomes, but the overall speed is the same, int/bigint is slightly faster as a native type 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 equal-length strings, varchar has one extra byte, so the performance is possible Will have some impact (1/n).
In terms of data operations and comparisons, integers benefit from native support, so they are slightly faster than stringsA little bit.
If indexing is used, the so-called performance gap between integers and strings is even more negligible.

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

The above is the detailed content of Performance comparison of int, char and varchar in MySQL. For more information, please follow other related articles on the PHP Chinese website!