In an accident, I have a new understanding of whether MySQL timestamp is stored in char(10) or int(10)

Abstract: char type fields want to go index, must be enclosed in quotation marks. If it is a pure number such as a timestamp, it is recommended to save it as an int type.

This article is shared from the HUAWEI cloud community " an accident, I have a new understanding of whether MySql timestamp saves char(10) or int(10) 160bae1b162771", the original author: Ben

Good friday

Everything was so beautiful on Friday morning.

However, at about 10 o’clock, the operating brother suddenly told me that the backend could not be opened. I thought "what's the big deal, maybe he won’t connect to wifi again" and opened the website confidently. Sure enough , I can't open it anymore.

This is deliberately making me a bad weekend!

Catch that bug

After my careful investigation, I found that a call to the "Get users logged in before today" interface seriously timed out:

In fact, this interface does not call many data tables. Only one table is read in MySQL. The table structure is as follows:

The SQL to get the list of users logged in before today is as follows:

SELECT u.email, log.user_id
FROM `user` u
LEFT JOIN `log_user_active` log ON u.user_id = log.user_id
WHERE log.`log_dtime` <1634567890
LIMIT 0 , 30

This is just a simple SQL query. Why is there no sophisticated and complex query so slow? Since log_user_active has the largest amount of data, it is guessed that there should be a problem with the log_user_active table. In order to troubleshoot the cause, I simplified the SQL again, removing the JOIN and simplifying it directly to:

SELECT log.user_id
FROM `log_user_active`
WHERE `log_dtime` <1551784072
LIMIT 0 , 30

After execution, this statement took nearly 1 second. . . If multiple people access at the same time, it is strange that MySQL does not crash.

At this point, you should be sure that there is no problem with this table, but the field log_dtime is clearly indexed, why is it so slow?

After various Baidus, I finally found the problem: because log_dtime is designed with char type. If you want it to be indexed, the value must be quoted when querying, indicating that this is a string, otherwise it will not be indexed. My data happens to be composed of numbers (time stamps), and I didn't deliberately add quotation marks when querying, which resulted in not indexing when querying.

This is the problem, so try the following:

Attempt 1:

SQL value enclosed in quotation marks

As shown in the picture above, it was really fast.

But in this case, a lot of code needs to be changed. I think I should try method 2.

Attempt 2:

Resolutely design the data table structure log_dtime as INT type, as shown in the figure:

Execute SQL again:

SELECT log.user_id
FROM `log_user_active`
WHERE `log_dtime` <1551784072
LIMIT 0 , 30

The corresponding result is improved by N times:

At this point, the problem is solved.

to sum up

If the char type field wants to be indexed, it must be enclosed in quotation marks. If it is a pure number such as a timestamp, it is recommended to save it as an int type.

Happy weekend, beckoned to me again.

Click to follow, and learn about the fresh technology of Huawei Cloud for the first time~