SQL Query execution sequence in WHERE clause

http://stackoverflow.com/questions/8686289/sql-query-execution-sequence-in-where-clause

SQL Query execution sequence in WHERE clause

up vote4down votefavorite

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.

sql

shareimprove this question

asked Dec 31 '11 at 4:33

nirav

1331518

 

add a comment

3 Answers

activeoldestvotes

up vote6down voteaccepted

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.

shareimprove this answer

edited Dec 31 '11 at 5:14

nirav

1331518

answered Dec 31 '11 at 4:37

dasblinkenlight

479k42526893

 

 

Assume that both columns are non-indexed, then what will be executed first.(as this is an example I am showing only 2 conditions to match, but what if there are so many AND conditions)? – nirav Dec 31 '11 at 4:46

1 

@nirav.patel In the absence of indexes RDBMS is free to pick whatever order it calculates would give you the best performance. Some RDBMS engines (e.g. Oracle) would calculate statistics on the dispersion of your data to pick the best order. For example, if RDBMS knows that your ID column has 40000 distinct values in a table of 40000 rows, and first name has 3000 distinct values, it would look at the ID first, because it promises a more specific answer. – dasblinkenlight Dec 31 '11 at 5:01

 

And how can one find which columns are indexed in pl/sql? – Java GWT Developer Apr 14 '16 at 16:15

add a comment

up vote2down vote

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.

shareimprove this answer

edited Jan 1 '12 at 8:25

answered Jan 1 '12 at 8:14

Somnath Muluk

21.6k14130152

 

add a comment

up vote0down vote

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.

shareimprove this answer

answered Dec 31 '11 at 4:37

jklemmack

2,24911538

 

 

But if the AND conditions are more say 15 so in that case what will be the sequence. – nirav Dec 31 '11 at 4:45