CODEWITHSUNDEEP
sqlperformance
Mike
Mike

Reputation: 14616

Performance of SQL query with condition vs. without where clause

Which SQL-query will be executed with less time — query with WHERE-clause or without, when:

  1. WHERE-clause deals with indexed field (e.g. primary key field)
  2. WHERE-clause deals with non-indexed field

I suppose when we're working with indexed fields, thus query with WHERE will be faster. Am I right?

Upvotes: 1

Views: 6754

Answers (5)

Kashif Nizam
Kashif Nizam

Reputation: 1

Some of my queries are quite complex and applying a where clause degrading the performance. For the workaround, I used temp tables and then applied where clause on them. This significantly improved the performance. Also, where I had joins especially Left Outer Join, improved the performance.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

There are cases where a where clause on the primary key will be slower.

The simplest is a table with one row. Using the index requires loading both the index and the data page -- two reads. No index cuts the work in half.

That is a degenerate case, but it points to the issue -- the proportion of the rows selected. Or, more accurately, the proportion of pages needed to resolve the query.

When the desired data is on all pages, then using an index slowed things down. For a non primary key, this can be disastrous, when the table is bigger than the page cache and the accesses are random.

Since pages are ordered by a primary key, the worst case is an additional index scan -- not too bad.

Some databases use statistics on tables to decide when to use an index and when to do a full table scan. Some don't.

In short, for low selectivity queries, an index will improve performance. For high selectivity queries, using an index can result in marginally worse performance or dire performance, depending on various factors.

Upvotes: 1

HLGEM
HLGEM

Reputation: 96640

It may depend on the type of where clause you are writing. In a simple where clause, it is generally better to have an index on the field you are using (and uindexes can and should be built on more than the PK). However, you have to write a saragble where clause for the index to make any difference. See this question for some guidelines on sarability:

What makes a SQL statement sargable?

Upvotes: 0

Science_Fiction
Science_Fiction

Reputation: 3443

As has been mentioned there is no fixed answer to this. It all depends on the particular context. But just for the sake of an answer. Take this simple query:

 SELECT first_name FROM people WHERE last_name = 'Smith';

To process this query without an index, every column, last_name must be checked for every row in the table (full table scan).

With an index, you could just follow a B-tree data structure until 'Smith' was found.

With a non index the worst case looks linear (n), whereas with a B-tree it would be log n, hence computationally less expensive.

Upvotes: 3

ryanbwork
ryanbwork

Reputation: 2153

Not sure what you mean by 'query with WHERE-clause or without', but you're correct that most of the time a query with a WHERE clause on an indexed field with outperform a query whose WHERE clause on a non-indexed field.

One instance where the performance will be the same (ie indexing doesn't matter) is when you run a range based query in your where clause (ie WHERE col1 > x ). This forces a scan of the table, and thus will be the same speed as a range query on a non indexed column.

Really, it depends on the columns you reference in the where clause, the types of data in the columns, the types of queries your running, etc.

Upvotes: 1

Related Questions