Search code examples
countexistssap-ase

Will Count(*) on unique index stop after 1 record found?

I have inherited some dodgy looking SQL which, when looking for a row with a unique index, uses COUNT(*) = 1 instead of using EXISTS

I know I'm being optimistic but will the query be smart enough to stop counting rows if its looking for a unique index and it finds a record?

I have tried to test this and it seems that the answer is no, but I was looking for a definitive answer before beginning the process of changing this code.

We use Sybase ASE 15.7

Solution

  • Actually the answer is yes.

    I did a query plan analysis and it stated:

      Forward Scan.
  Positioning by key.
  Index contains all needed columns. Base table will not be read.

    Actually this seems clear now as the query will obviously use the index and then the aggregate is simply counting the result of that query.