What is the respective performance of int, char and varchar in MySQL

This article mainly introduces the respective performance of int, char and varchar in MySQL. The content of the article is carefully selected and edited by the author. It has certain pertinence and reference significance for everyone. The following is to understand the respective performance of int, char and varchar in MySQL.

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

I recently ran a performance test on int, long, char, and varchar, and found that they don't really have much of a performance gap:

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

100w rows query without index:

Perform [c8 query]20 times, average time 312.0ms

Executing [s8 query]20 times, average time 334.3ms

Executing [i8 query]20 times, average time 276.95ms

Perform [c4 query]20 times, average time 354.95ms

Perform [s4 query]20 times, average time 340.45ms

Executing [i4 query]20 times, average time 291.1ms

Create index:

c8 indexing takes 2439ms

s8 indexing takes 2442ms

i8 indexing takes 1645ms

c4 Indexing takes 2296ms

s4 Indexing takes 2303ms

i4 indexing takes 1403ms

Query with index:

Executing [c8 query]10000 times, average time 0.271ms

Executing [s8 query]10000 times, average time 0.2354ms

Executing [i8 query]10000 times, average time 0.2189ms

Executing [c4 query]10000 times, average time 0.303ms

Executing [s4 query]10000 times, average time 0.3094ms

Executing [i4 query]10000 times, average time 0.25ms

Conclusion:

Indexless: Full table scans are not faster because the data is smaller, but overall the same speed, int/bigint as a native type slightly faster 12%.

Indexed: char and varchar performance is similar, int speed is slightly faster 18%

In terms of data storage, reading and writing, integers are the same as strings of equal length, and 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 index is used, the performance gap between so-called integer and string is even smaller.

In actual development, many developers often use char(1), char(4) such strings to represent the type enumeration, which in my opinion is the best solution, because this approach in storage space, computing performance, readability, maintainability, extensibility, far better than int, enum this data type.

After reading the above about the respective performance of int, char and varchar in MySQL, many readers must have some understanding, if you need to obtain more industry knowledge information, you can continue to pay attention to our industry information column.