:Solution to timeout interruption of SQL statements executed on the SQL Console page

Problem description

An execution timeout occurs when you execute an SQL statement on the SQL Console page of DMS. The execution result displays the following information:

[Statement 1]:
select count(*) from table_name where gmt_create>='2020-01-01';
Failed, details: Operation terminated (user interrupted or execution timed out)
[Solution]:
1. Add an indexed column in the query condition to improve the query performance.
2. Narrow the query scope as much as possible.
3. If the instance needs security collaboration, you can submit a schema design ticket to add indexes to the query columns.
4. Click edit instance to reset the query timeout value.
TraceId : 0bc059b716002426281173397e58b3

Solution

Note: the SQL statements in this article are used as examples. The details are subject to the actual environment.

After the problem occurs, you can solve it by:

1. You can add an indexed column in the query condition to improve query performance.
   If the user_id field has an index, you can add query conditions to confirm that the query time is shorter.
   

   select count(*) from table_name where gmt_create>='2020-01-01' and user_id = '1000';

   
   
   
2. Narrow the query scope as much as possible.
   You can add query conditions by using the WHERE clause. To narrow down the query scope, add gmt_create as follows:

   select count(*) from table_name where gmt_create>='2020-01-01' and gmt_create<'2020-04-01';
   

3. In a security instance, you can schema design or data changes to enable indexes on queried columns. After adding an index, you can add conditions for the index Field to query.
   • Structural design:
     You can configure the gmt_create field through schema design. For more information, see schema design.
     
     
   • Data changes:
     The following SQL statement is used to add an index to the gmt_create field:

     alter table table_name add index idx_gmt(gmt_create);

4. You can edit the instance to change the query timeout period.
   1. On the DMS page, find the target instance. Right-click the instance ID and choose edit instance from the shortcut menu.
      
   2. Click advanced information, according to the actual situation to adjust the query time-out time of the value and then click submitted that may.

      Note: in this topic, the query timeout is 60s as an example.

      
      

Scope

• Data Management