db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: How to efficiently check for existence of a row?
Date Mon, 20 Oct 2008 08:55:47 GMT
Amir Michail <amichail@gmail.com> writes:

> Hi,
>
> Given some condition, how do you efficiently check whether there is at
> least one row that satisfies that condition?
>
> count(*) is slow.

To check whether there are any rows in the table T where X < 4, you
could do something like this:

  VALUES 1 INTERSECT SELECT 1 FROM T WHERE X < 4
  (returns one row if there's a match, zero rows otherwise)

This should stop immediately when it finds a matching row. If there are
no matching rows, the entire table will be scanned unless you have an
index on the column X.

-- 
Knut Anders

Mime
View raw message