How to Rewrite TiDB Prepared Statements with Multiple Variables and unix_timestamp()

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TiDB预处理Prepare / Execute多个变量unix_timestamp()如何使用改写

| username: TiDB_C罗

[TiDB Usage Environment] Production Environment
[TiDB Version] v5.4.1
[Encountered Problem: Phenomenon and Impact]
By checking TiDB Dashboard, there are many similar slow queries. The execution time of one of the slow queries is as follows:

image
image1380×484 47.4 KB

Generating the execution plan consumed most of the time.
Thought of rewriting the query using the Prepare / Execute method.
Due to the large number of unix_timestamp() functions in the slow query, the execution plan cannot be cached.

   [test]>select @@last_plan_from_cache;
    +------------------------+
    | @@last_plan_from_cache |
    +------------------------+
    |                      0 |
    +------------------------+

Check Grafana->TiDB->Executor->Queries Using Plan Cache OPS

image
image1380×598 19.4 KB

If unix_timestamp() is replaced with ?, execute st using @time1,@time2,time3…; there are too many parameters. Seeking other rewriting solutions. Thank you!

2
| username: Billmay表妹 | Original post link

If you want to use the unix_timestamp() function in TiDB to replace multiple variables in a Prepare/Execute statement, you can use the following methods:

  1. Use the unix_timestamp() function in the Prepare statement to get the current timestamp, for example:
PREPARE stmt FROM 'SELECT * FROM table WHERE time > ?';
SET @time = UNIX_TIMESTAMP();
EXECUTE stmt USING @time;
  1. If you need to use multiple variables in the Prepare/Execute statement, you can use multiple ? placeholders to represent these variables, for example:
PREPARE stmt FROM 'SELECT * FROM table WHERE time > ? AND id = ?';
SET @time = UNIX_TIMESTAMP();
SET @id = 1;
EXECUTE stmt USING @time, @id;

In this example, the ? placeholders represent variables in the Prepare/Execute statement, and @time and @id are user variables in TiDB used to store the timestamp and ID values, respectively.

In summary, you can use the unix_timestamp() function to replace multiple variables in a Prepare/Execute statement, and use multiple ? placeholders to represent these variables.

3
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.