On 28 Nov, 10:34, Daniel Marcinkowski <daniel.marcinkow...@gmail.com>
wrote:
> Hi all,
>
> in my database I have a very large table. I want to check one column of
> this table if there exists a nonzero value in it. The ideas I came up
> with is counting or using the max function. However, these functions
> have to check all values in a column. The most efficient way would be to
> use a function which just scans for the first nonzero value and then, if
> one was found, returns a true (or 1).
>
> Does anyone have an idea?
> --
> Dan

"The most efficient way would be to use a function which just scans
for the first nonzero value and then, if one was found, returns a true
(or 1)."
On what basis do you say that?

Why not create an index on the column and just use something like
SELECT
1
WHERE column > 0
LIMIT 1

(If values can be negative too add "OR column < 0" at the end of teh
WHERE clause)

On Nov 28, 4:34 am, Daniel Marcinkowski
<daniel.marcinkow...@gmail.com> wrote:
> Hi all,
>
> in my database I have a very large table. I want to check one column of
> this table if there exists a nonzero value in it. The ideas I came up
> with is counting or using the max function. However, these functions
> have to check all values in a column. The most efficient way would be to
> use a function which just scans for the first nonzero value and then, if
> one was found, returns a true (or 1).
>
> Does anyone have an idea?
> --
> Dan

How about

SELECT column
FROM table
WHERE column <> 0

?

On Wed, 28 Nov 2007 20:06:19 +0100, <lawpoop@gmail.com> wrote:

> On Nov 28, 4:34 am, Daniel Marcinkowski
> <daniel.marcinkow...@gmail.com> wrote:
>> Hi all,
>>
>> in my database I have a very large table. I want to check one column of
>> this table if there exists a nonzero value in it. The ideas I came up
>> with is counting or using the max function. However, these functions
>> have to check all values in a column. The most efficient way would beto
>> use a function which just scans for the first nonzero value and then,if
>> one was found, returns a true (or 1).
> How about
>
> SELECT column
> FROM table
> WHERE column <> 0

Which would select all rows, and the OP just wants to know 'wether there
are any rows with a non-zero value' in the most efficient way possible.

I agree with the Captain, add a key to the column, and then run a query
(like yours), limited to one:
SELECT column FROM table WHERE column != 0 LIMIT 1
--
Rik Wasmus

== Quote from Rik Wasmus (luiheidsgoeroe@hotmail.com)'s article
> On Wed, 28 Nov 2007 20:06:19 +0100, <lawpoop@gmail.com> wrote:
> > On Nov 28, 4:34 am, Daniel Marcinkowski
> > <daniel.marcinkow...@gmail.com> wrote:
> >> Hi all,
> >>
> >> in my database I have a very large table. I want to check one column =
> of
> >> this table if there exists a nonzero value in it. The ideas I came up=
> >> with is counting or using the max function. However, these functions
> >> have to check all values in a column. The most efficient way would be=
> to
> >> use a function which just scans for the first nonzero value and then,=
> if
> >> one was found, returns a true (or 1).
> > How about
> >
> > SELECT column
> > FROM table
> > WHERE column <> 0
> Which would select all rows, and the OP just wants to know 'wether there=
> =
> are any rows with a non-zero value' in the most efficient way possible.
> I agree with the Captain, add a key to the column, and then run a query =
> =
> (like yours), limited to one:
> SELECT column FROM table WHERE column !=3D 0 LIMIT 1
> -- =
> Rik Wasmus


it's bit unclear what he wants; he says nonzero value in it. this could be
construed as a nonzero value in the column, something like "123". what if he wants
a hit on a value such as this: "102". the other thing he's saying is: "have to
check all values in a column" which again is a bit murky. maybe there is a bit of
language barrier in here.

ya'll jump the gun without knowing exactly what he wants.
--
POST BY: lark with PHP News Reader ;o)

Captain Paralytic wrote:

>
> You mean "add a 'LIMIT 1'" just like the LIMIT 1 I already had in the
> first response to this thread huh!

Yes indeed sir!

Norm