What is faster?

Mihai Gheorghiu wrote:

What runs faster?
select where timestamp IS NOT NULL
or
select where boolean IS TRUE?

It depends..... (You knew somebody would say that, didn't you? :-))

If you return a lot of data, then the statement that returns the least data is likely to be the fastest.

Otherwise, on my 6M+ records test database, it seems to be about the same. Maybe the boolean select is a little faster

real 0m12.969s
user 0m0.020s
sys 0m0.000s

vs.

real 0m13.326s
user 0m0.000s
sys 0m0.010s

for four records returned?

Does it make a significant difference if the boolean is indexed? There are
many records in the table.

Not if there are about even number of the values, but if there is only a few of the one you search for, it will help a lot. In my test it reduces the query time to

real 0m0.315s
user 0m0.010s
sys 0m0.000s

for the first query and

real 0m0.038s
user 0m0.010s
sys 0m0.000s

when it is repeated (and the index presumably is cached in memory)

    --- Allan.

On Mon, Sep 17, 2001 at 10:05:32PM +0100, Allan Engelhardt wrote:

Mihai Gheorghiu wrote:

What runs faster?
select where timestamp IS NOT NULL
or
select where boolean IS TRUE?

It depends..... (You knew somebody would say that, didn't you? :-))

If you return a lot of data, then the statement that returns the least
data is likely to be the fastest.

Except for the fact that IS NOT NULL cannot use an index whereas IS TRUE can
(I beleive, = 't' definitly can).

--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/