Welcome to WebmasterWorld Guest from 54.242.83.92

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

best way to optimize: in query or php?

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

Full Member

10+ Year Member

joined:Apr 30, 2006
posts:298
votes: 0


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?

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

Junior Member

10+ Year Member

joined:Apr 17, 2006
posts:195
votes: 0


Hi pixeltierra!
I could use a query like this:

SELECT * FROM table WHERE field <> 0

or:

SELECT * FROM table WHERE field > 0

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

Full Member

10+ Year Member

joined:Apr 30, 2006
posts:298
votes: 0


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';

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

Junior Member

10+ Year Member

joined:Apr 17, 2006
posts:195
votes: 0


SELECT * is used to select all fields in the table

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

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

Full Member

10+ Year Member

joined:Apr 30, 2006
posts:298
votes: 0


>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?

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

Senior Member from US 

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

joined:Apr 19, 2003
posts:4393
votes: 2


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?

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

Full Member

10+ Year Member

joined:Apr 30, 2006
posts:298
votes: 0


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.

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

Administrator

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

joined:July 24, 2001
posts:15755
votes: 0


I think your best bet then would be to do the exclusion using php
8:04 am on Sept 19, 2006 (gmt 0)

Preferred Member

10+ Year Member

joined:Apr 30, 2005
posts:515
votes: 0


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!

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

Full Member

10+ Year Member

joined:Apr 30, 2006
posts:298
votes: 0


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

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

Junior Member

10+ Year Member

joined:Jan 13, 2006
posts:165
votes: 0



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.
8:11 pm on Sept 21, 2006 (gmt 0)

Junior Member

5+ Year Member

joined:May 17, 2006
posts:41
votes: 0


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

with an index on field1,field2,field3,etc

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

Full Member

10+ Year Member

joined:Apr 30, 2006
posts:298
votes: 0


Vali: Sorry, I don't think I got your meaning...
4:06 am on Sept 28, 2006 (gmt 0)

Junior Member

5+ Year Member

joined:May 17, 2006
posts:41
votes: 0


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.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members