CODEWITHSUNDEEP
sql
nirav
nirav

Reputation: 373

SQL Query execution sequence in WHERE clause

What is the excution order in Query like:

SELECT * FROM [users] WHERE [userid] = 50001 AND [username] = 'new user'

My question is what will be matched for first - [userid] or [username].

and so will affect the execution time.

Any suggesetion to improve this query will be appriciated.

Upvotes: 5

Views: 11220

Answers (4)

user22121490
user22121490

Reputation: 1

In case of both being non-indexed columns If we assume the WHERE clause is resolved as sequenced in the query. Then logically, the sequence of WHERE clause columns should be in decreasing order of count(distinct values) of that column

eg. count(distinct values of column_a) = 1000 count(distinct values of column_b) = 500 query : select * from table_name WHERE column_a='california' AND column_b = 'male'

Upvotes: 0

Somnath Muluk
Somnath Muluk

Reputation: 57836

The database will decide what order to execute the conditions in.

Normally (but not always) it will use an index first where possible.

You can see how conditions in where or joins are needs to be optimized: -SQLStatementExecution -Example Discussion1 -Example Discussion2 -Example Discussion3

Generally speaking, the order of criteria in the WHERE clause is evaluated and optimized by the query optimizer prior to creating an execution plan. This is good. However, I would encourage you to review the query execution plan for each query prior to putting it into production.

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 727067

The answer depends on the indexes that you make available to the SQL engine. If userid is indexed but username is not, the engine is likely to start with userid; if username is indexed but userid is not, then the engine would probably start with the name; if both fields are indexed, the engine will search that index, and look up the rows by internal ids. Note that all of this is highly dependent on the RDBMS that you are using. Some engines would forego index searches altogether in favor of full table scans when the number of rows is low.

Upvotes: 8

jklemmack
jklemmack

Reputation: 3636

SQL-based database engines will generally optimize based on the clustered (the physical order of data records) and any available indexes. MySQL and MS SQL Server (at a minimum - many others are too) are smart enough to know which order to execute filters to optimize a query.

For your purposes, it doesn't matter and the execution results will be the exact same, with the same performance, in either order.

Upvotes: 1

Related Questions