Compare the performance of int, char and varchar in MySQL

Time:2019-6-21

There are many paradoxical “rumors” on the Internet, of course, are not malicious. Most of them are developers who are unwilling to take the initiative to study, but believe 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 for int, long, char and varchar, and found that they did not have much performance gap.

Note: C8 = char (8), S8 = varchar (8), I8 = bigint, C4 = char (4), S4 = varchar (4), I4 = char (4)

Query without index for 100W rows:

Executing [c8 queries] 20 times, average time-consuming 312.0 MS
Executing [s8 queries] 20 times, with an average time of 334.3ms
Twenty [i8 queries] were executed, with an average time of 276.95 MS
Executing [c4 queries] 20 times with an average time of 354.95 MS
Execution of [s4 query] 20 times, average time consuming 340.45 MS
Performing [i4 queries] 20 times, with an average time of 291.1 MS

Create an index:

C8 index takes 2439 MS
S8 index takes 2442 MS
I8 index takes 1645 MS
C4 index takes 2296 MS
S4 index takes 2303 MS
I4 index takes 1403 MS

Queries with indexes:

Execution of [c8 queries] 10,000 times, with an average time of 0.271 MS
10,000 executions of [s8 queries], with an average time of 0.2354 MS
10,000 times of execution of [i8 queries], with an average time of 0.2189ms
Execution of [c4 queries] 10,000 times, with an average time of 0.303 MS
10,000 executions of [s4 queries], with an average time of 0.3094 MS
10,000 executions of [i4 queries] with an average time of 0.25 MS

Conclusion:

Indexed: Full table scanning is not faster because the data is smaller, but the overall speed is the same, int/bigint as the original type is slightly faster by 12%.

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

In terms of data storage, read and write, integers are the same as equal-length strings, and varchar adds an extra byte, so performance may be slightly affected (1/n).

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

If index is used, the so-called performance gap between integers and strings is very small.

In practical development, many developers often use char (1), char (4) to represent type enumeration, which seems to me to be the best solution, because this approach in storage space, computing performance, readability, maintainability, scalability, far better than int, enum data type.

Recommended Today

Lua language novice simple tutorial

1、 Foreword Lua is a lightweight and compact scripting language, which is written in standard C language and open in the form of source code. Its design purpose is to be embedded in the application, so as to provide flexible expansion and customization functions for the application. Lua can be applied in game development, independent […]