How to compare the performance of int, char and varchar in MySQL

Editor to share with you how to compare the performance of int, char and varchar in MySQL. I hope you will get something after reading this article. Let's discuss it together.

There are many specious "rumors" on the Internet, of course, are not malicious, most of them are developers are not willing to take the initiative to study, but credulous to other people's words.

There are also a lot of rumors about databases, such as "int performs much better than char".

I recently conducted a performance test on int, long, char, and varchar, and found that there was 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 100w rows without index:

Execute [c8 query] 20 times, taking an average of 312.0ms

Execute [S8 query] 20 times, taking an average of 334.3ms

Execute [i8 query] 20 times, taking an average of 276.95ms

Execute [c4 query] 20 times, taking an average of 354.95ms.

Execute [S4 query] 20 times, taking an average of 340.45ms.

Execute [i4 query] 20 times, taking an average of 291.1ms

Create an index:

C8 index time-consuming 2439ms

S8 index time-consuming 2442ms

I8 index time-consuming 1645ms

C4 index time-consuming 2296ms

S4 index time-consuming 2303ms

I4 index time-consuming 1403ms

Query with index:

Execute [c8 query] 10000 times, taking an average of 0.271ms

Execute [S8 query] 10000 times, taking an average of 0.2354ms

Execute [i8 query] 10000 times, taking an average of 0.2189ms

Execute [c4 query] 10000 times, taking an average of 0.303ms

Execute [S4 query] 10000 times, taking an average of 0.3094ms

Execute [i4 query] 10000 times, with an average time of 0.25ms

Conclusion:

No index: full table scans are not faster because the data is small, but the overall speed is the same. As a native type, int/bigint is slightly faster by 12%.

Indexed: char has the same performance as varchar, and int is slightly faster by 18%.

In terms of data storage, reading and writing, integers are the same as equal-length strings, and varchar has an extra byte, so performance may have a slight impact (1).

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

If the index is used, the performance gap between the so-called 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, which in my opinion is the best solution, because it is far better than int and enum data types in terms of storage space, computing performance, readability, maintainability and extensibility.

After reading this article, I believe you have a certain understanding of "how to compare the performance of int, char and varchar in MySQL". If you want to know more about it, welcome to follow the industry information channel, thank you for reading!