best way to optimize: in query or php?

[pixeltierra]

I have a db table with 40 fields, any of which can be '0' (not null). I want to select all rows that don't have a single '0' value in any field.

Writing my query, I couldn't find a simple way to do this. So I'll select '*' and make a php loop to weed out rows with '0' values.

Only there must be a better way using SQL I imagine. Any ideas?

[Psychopsia]

Hi pixeltierra!
I could use a query like this:

SELECT * FROM table WHERE field <> 0

or:

SELECT * FROM table WHERE field > 0

[pixeltierra]

Does 'field' in your sql statement mean all fields, or a specific field? What I want is all fields, without having to list them all.

Below is sample data that I would not want to select because they have zero (anywhere) in them.

3 3 0 4 0 5 5 5 4 4 0 5...
2 2 1 2 2 1 2 0 2 1 2 2...
2 1 1 2 2 3 4 3 2 2 0 4...

I've looked in my mysql documentation and can't find an 'all fields' operator, except '*'

However this threw an error:

SELECT * FROM table WHERE *!= '0';

[Psychopsia]

SELECT * is used to select all fields in the table

In the WHERE is invalid to put *, you have to write a field name.

[pixeltierra]

>In the WHERE is invalid to put *, you have to write a field name.

Right. So back to the initial question. Is there a way to optimize what I want to select (all rows that don't contain any 0s) in the query? I don't consider a list of forty [field!= 0]s to be optimal.

So I'm using a loop in php:

foreach ($all_data as $key => $row){
foreach ($row as $val){
if ($val == 0 ¦¦ $val == '0'){
array_push($bad_data, $row);
break;
}
}
}

I guess the question boils down to is there a way in sql to represent "any given field value" in a where clause?

[henry0]

Well, sure there might be more to your logic that I don't get :)

But if any value is ok
then why using a where clause?

[pixeltierra]

Thanks for your help on this guys, but I guess I'm not being very clear. Essentially I want an sql statment that returns a row as long as none of the field values is = to '0'

If it's true that I have to list every field by name and make sure it's!= 0, that gets ugly. Since there are 40 fields I would have where x!=0 AND y!=0 AND z!=0 etc... 40 times. That's ugly and inefficient.

[jatar_k]

I think your best bet then would be to do the exclusion using php

[FalseDawn]

Depending on how often the data is updated and how often the query is run, I'd consider adding an additional column that is the product of the 40 columns - i.e. c1 * c2 * c3... * c40

The beauty of this is that this column will be zero if and only if at least one of your 40 columns are zero. This column can also be indexed, and the subsequent retrieval query will be short and fast.

The downside of course is data redundancy and the need to make sure this column is always synchronized with your 40 other columns!

[pixeltierra]

FalseDawn: An interesting idea! Very creative. At the very least this:

field1 * field2 * field3 * field4...!= 0

is cleaner than this

field1!= 0 AND field2!= 0 AND field3!= 0 AND field4!= 0 AND...

[Moosetick]

FalseDawn: An interesting idea! Very creative. At the very least this:
field1 * field2 * field3 * field4...!= 0

is cleaner than this

field1!= 0 AND field2!= 0 AND field3!= 0 AND field4!= 0 AND...

I'm not sure but I think it would be more efficient using the AND statements. By multiplying 40 numbers you could be putting a huge strain on the server. 2^40=1099511627776. If those numbers are larger you could be forcing the server to go through massive computations.

[Vali]

field1!= 0 AND field2!= 0 AND field3!= 0 AND field4!= 0 AND...

with an index on field1,field2,field3,etc

[pixeltierra]

Vali: Sorry, I don't think I got your meaning...

[Vali]

You make a huge index, for all those fields.

And you select with the WHERE.

Only problem will be that the more/bigger indexes you have, the slower your inserts will get. (So an index on 40 fields is pretty big.)

Another way to do it, is to put all 40 fields in one, then you would just do:
SELECT bla FROM bla WHERE field = '111111111'; (well 40 times 1)
(And you would have an index set on "field")

I say this is the best way.