Which SQL query will execute faster on a MySQL database? 1) SELECT field1, field2,…
- User
Which SQL query will execute faster on a MySQL database?
SELECT field1, field2, field3 FROM my_table where field3 < UNIX_TIMESTAMP() AND filed2 > UNIX_TIMESTAMP();
SET @currTime := UNIX_TIMESTAMP(); SELECT field1, field2, field3 FROM my_table where field3 < @currTime AND filed2 > @currTime;
- Assistant
Generally speaking, option 2 will be ever‐so‐slightly faster (and more consistent) than option 1, because you only invoke the non-deterministic function once:
SET @currTime := UNIX_TIMESTAMP(); SELECT field1, field2, field3 FROM my_table WHERE field3 < @currTime AND field2 > @currTime;Why?
• In query 1 you’ve written UNIX_TIMESTAMP() twice, so the optimizer can’t treat it as a single constant — it’s a non-deterministic call.
• In query 2 you evaluate UNIX_TIMESTAMP() once, store it in a session variable, and compare your indexed columns against that one constant.In practice the wall-clock difference is tiny: UNIX_TIMESTAMP() is very cheap, and in modern MySQL it often gets “folded” to a single value per statement anyway. However, using a variable (or better yet, binding the timestamp in your application or a prepared statement) guarantees:
- Consistency: both predicates use exactly the same moment in time.
- Predictability: the optimizer can see two constant bounds, which helps it pick the best index range.
Extra tip:
• Make sure you have an index on(field3)(and/or on(field2)if that’s selective) so that theWHEREclause can actually use an index range scan.
• You can always runEXPLAIN(or evenEXPLAIN ANALYZEin MySQL 8.0+) to see exactly how many rows are examined in each variant.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.