1: SQL execution time of 0.826 seconds
SELECT
Tenant_id,
O2o_user_id
The FROM
Crm_point_sync_i2o
WHERE sync_status=0
AND next_execute_time & lt; NOW ()
AND tenant_id IN
(SELECT
Tenant_id
The FROM
Crm_config
WHERE is_enable=1
AND is_sync=1)
The ORDER BY try_times ASC,
Update_time DESC
LIMIT 1000;

SQL2, on the basis of sql1 remove the sort, by rights, should be faster ah, how slow so much: the execution time of 2.212 seconds
SELECT
Tenant_id,
O2o_user_id
The FROM
Crm_point_sync_i2o
WHERE sync_status=0
AND next_execute_time & lt; NOW ()
AND tenant_id IN
(SELECT
Tenant_id
The FROM
Crm_config
WHERE is_enable=1
AND is_sync=1)
LIMIT of 1000

Execution plan is as follows:

CodePudding user response:

Have a LIMIT of 1000;
This changed the data sequence, so do not add the ORDER BY not necessarily fast

CodePudding user response:

reference 1st floor ZJCXC response:

1000 LIMIT;
This changed the data sequence, so do not add the ORDER BY not necessarily fast

Do not add the order by not fast, so why add much faster than all the time

CodePudding user response:

refer to the second floor xlycom response:

Quote: refer to 1st floor ZJCXC response:

Have a LIMIT of 1000;
This changed the data sequence, so do not add the ORDER BY not necessarily fast

Do not add the order by not fast, so why add is much faster than without

The size of difference and execution plan

CodePudding user response:

reference xlycom reply: 3/f

Quote: refer to the second floor xlycom response:

Quote: refer to 1st floor ZJCXC response:

Have a LIMIT of 1000;
This changed the data sequence, so do not add the ORDER BY not necessarily fast

Do not add the order by not fast, so why add is much faster than without

As big difference and execution plan

In addition, I now remove the limit, the effect is the same, with the order by a lot faster

CodePudding user response:

Don't know what hair nerve, see figure
Tell me about the theory of
ORDER BY or LIMIT could lead to an execution plan, so there are differences is normal efficiency, the key is to look at the situation of the execution plan is more conform to the requirements of efficiency
Execution plan is, after all, according to the statement and table structure statistical information assessment of a should be the best way of execution should be, but it is not absolutely
To although do not add the ORDER BY should be quicker, but this is not absolutely

CodePudding user response:

reference 5 floor ZJCXC reply:

don't know what hair nerve, see figure
Tell me about the theory of
ORDER BY or LIMIT could lead to an execution plan, so there are differences is normal efficiency, the key is to look at the situation of the execution plan is more conform to the requirements of efficiency
Execution plan is, after all, according to the statement and table structure statistical information assessment of a should be the best way of execution should be, but it is not absolutely
To although do not add the ORDER BY should be quicker, but this is not absolutely

How much do you QQ, I add you, QQ to discuss