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 vote 4 down vote favorite	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 share improve this question asked  Dec 31 '11 at 4:33 nirav 133 1 5 18
	add a comment

3 Answers

active oldest votes

up vote 6 down vote accepted

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. share improve this answer edited Dec 31 '11 at 5:14 nirav 133 1 5 18 answered  Dec 31 '11 at 4:37 dasblinkenlight 479k 42 526 893

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 vote 2 down 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. share improve this answer edited Jan 1 '12 at 8:25 answered  Jan 1 '12 at 8:14 Somnath Muluk 21.6k 14 130 152

add a comment

up vote 0 down 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. share improve this answer answered  Dec 31 '11 at 4:37 jklemmack 2,249 1 15 38

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