Thread: 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.