2

three examples like this: table had 300,000 data,all three are consumption 0.3s

cast function

SELECT CREATE_TIME from repay_plan 
where CREATE_TIME>cast('2019-05-31 00:00:00' as datetime)
ORDER BY REPAY_ID desc limit 10000;

str_to_date function

SELECT CREATE_TIME from repay_plan 
where CREATE_TIME>str_to_date('2019-05-31 00:00:00','%Y-%m-%d %H:%i:%S')
ORDER BY REPAY_ID desc limit 10000;

varchar

SELECT CAST(CREATE_TIME AS char) from repay_plan 
where CREATE_TIME>'2019-05-31 00:00:00'
ORDER BY REPAY_ID desc limit 10000;
Community
  • 1
  • 1
cloudware
  • 21
  • 1
  • Hi, Welcome to StackOverflow aka. "SO" Glad to have you apart of the community! Did you try searching SO? [I found this for you.](https://stackoverflow.com/a/8093544/1896134) Also, Please visit our [How to Ask](https://stackoverflow.com/help/how-to-ask), as this will guide you in getting more support from us in the community. – JayRizzo Jul 13 '19 at 08:57
  • Also, What are your `Explain Plan` on all three queries? – JayRizzo Jul 13 '19 at 08:59

1 Answers1

0

Comparing the create time directly against a valid MySQL datetime literal is by far the most desirable thing to do. Your third version basically is doing this. Here is your third version slightly modified:

SELECT CREATE_TIME
FROM repay_plan 
WHERE  CREATE_TIME > '2019-05-31 00:00:00'  -- no cast or conversion necessary
ORDER BY REPAY_ID DESC
LIMIT 10000;

This version will be faster than the first two versions, because it avoids the unnecessary date conversion function calls. It also leaves the WHERE clause sargable, perhaps the biggest gain, which would allow MySQL to use an index on the CREATE_TIME column.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • can you say more detail, please? why the third version （char） need not convert ？some links is best – cloudware Jul 13 '19 at 09:34
  • Please check the MySQL [documentation](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html) to learn more about date and time literals. – Tim Biegeleisen Jul 13 '19 at 09:37