Обсуждение: What is faster?

What runs faster?
select   where   timestamp IS NOT NULL
or
select   where   boolean IS TRUE?
Does it make a significant difference if the boolean is indexed? There are
many records in the table.

Thank you for your help.

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/
> Magnetism, electricity and motion are like a three-for-two special offer:
> if you have two of them, the third one comes free.