Abstract:A char field must be enclosed in quotation marks if it is to be indexed. If it is a pure number, such as a timestamp, it is recommended to save it as an int.

Share this article from huawei cloud community “an accident, I to MySql timestamp saved char (10) or int (10) have a new understanding of the original author: 40 yards farmers.

Beautiful Friday

It was Friday morning and everything was fine.

However, more than 10 o ‘clock, the operation of the little brother suddenly told me that the background can not be opened, I with a “what’s the big deal, it is estimated that he will not even WiFi” mood, confident opened the website, as expected, really can not be opened.

This is just trying to spoil my weekend.

Catch that bug

After careful investigation, I found that a “get the user who logged in before today” interface call severely timed out:

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

SQL > get the list of users who logged in before today

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, there is no sophisticated, complex query why so slow? Since LOG_USER_ACTIVE has the largest amount of data, it is guess that there is a problem with the table LOG_USER_ACTIVE. In order to find out the reason, I simplify SQL again, remove JOIN and simplify it directly to:

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

This statement took nearly a second to execute… If more than one person is accessing MySQL at the same time, it will be surprising if MySQL doesn’t crash.

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

After a variety of baidu, finally found the problem: because log_dtime design is a char type. If you want it to be indexed, when you query it, the value has to be quoted to indicate that it’s a string, otherwise it won’t be indexed. My data happens to be made up of numbers (timestamps), and I didn’t put quotes around it, so I didn’t index it.

That’s the problem, so try this:

Try to 1:

The value of SQL is enclosed in quotes

As shown above, it is extremely fast.

But that would require a lot of code change, so I think I’ll try method 2 instead.

Try 2:

Decisively design the data table structure log_dtime as INT, as shown in the figure below:

SQL > select * from sysobjects

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

Corresponding results increased by N times:

At this point, the problem is solved.

conclusion

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

Happy weekend beckoned to me again.

Click on the attention, the first time to understand Huawei cloud fresh technology ~