5

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.

nirav
  • 373
  • 3
  • 7
  • 20

4 Answers4

8

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.

nirav
  • 373
  • 3
  • 7
  • 20
Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
  • 1
    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 04: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. – Sergey Kalinichenko Dec 31 '11 at 05:01
  • And how can one find which columns are indexed in pl/sql? – Ash S Apr 14 '16 at 16:15
2

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.

Community
  • 1
  • 1
Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
1

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.

jklemmack
  • 3,518
  • 3
  • 30
  • 56
0

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'

user22121490
  • 1