I have a set of conditions in my where clause like

WHERE id > 123456 
AND type LIKE 'type%'

The issue is that I would like the query to run the integer comparison first, as it signficantly decreases the amount of data that the string comparison needs to run.

However, the optimizer chooses to run the string comparison first forcing it to run extremely slowly.

Is there a way to force the execution order of my where clause?

CodePudding user response：

I don't know about forcing it to run in a certain order but what if you used a cte?

Pretty hacky but maybe workable?

WITH cte_yourdata AS
(
    SELECT  *
    FROM    yourTable
    WHERE   id > 123456 
)

SELECT  *
FROM    cte_yourdata
WHERE   type LIKE 'type%'

CodePudding user response：

SQL is declarative language hence it, in general, does not allow you to specify order of execution of conditions. You have several possibilities though:

• define index on id and not on type
• define index on both id and type and ensure id column is more selective (narrowing result) than type column. You haven't described your data.
• in Oracle, use /* INDEX(...)*/ hint to enforce proper index
• use materialized CTE

Please specify your db vendor and explain plan of your query for more precise answer.