Welcome to WebmasterWorld Guest from 107.20.34.173

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

best way to optimize: in query or php?

     

pixeltierra

5:16 pm on Sep 18, 2006 (gmt 0)

5+ Year Member



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

5:27 pm on Sep 18, 2006 (gmt 0)

5+ Year Member



Hi pixeltierra!
I could use a query like this:

SELECT * FROM table WHERE field <> 0

or:

SELECT * FROM table WHERE field > 0

pixeltierra

6:24 pm on Sep 18, 2006 (gmt 0)

5+ Year Member



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

7:10 pm on Sep 18, 2006 (gmt 0)

5+ Year Member



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

7:28 pm on Sep 18, 2006 (gmt 0)

5+ Year Member



>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

10:29 pm on Sep 18, 2006 (gmt 0)

WebmasterWorld Senior Member henry0 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

4:47 am on Sep 19, 2006 (gmt 0)

5+ Year Member



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

6:15 am on Sep 19, 2006 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

FalseDawn

8:04 am on Sep 19, 2006 (gmt 0)

10+ Year Member



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

5:33 pm on Sep 19, 2006 (gmt 0)

5+ Year Member



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

5:59 pm on Sep 20, 2006 (gmt 0)

5+ Year Member




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

8:11 pm on Sep 21, 2006 (gmt 0)

5+ Year Member



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

with an index on field1,field2,field3,etc

pixeltierra

11:28 pm on Sep 22, 2006 (gmt 0)

5+ Year Member



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

Vali

4:06 am on Sep 28, 2006 (gmt 0)

5+ Year Member



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.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month