homepage Welcome to WebmasterWorld Guest from 54.243.17.133
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
best way to optimize: in query or php?
pixeltierra




msg:3087628
 5:16 pm on Sep 18, 2006 (gmt 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?

 

Psychopsia




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

Hi pixeltierra!
I could use a query like this:

SELECT * FROM table WHERE field <> 0

or:

SELECT * FROM table WHERE field > 0

pixeltierra




msg:3087721
 6:24 pm on Sep 18, 2006 (gmt 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';

Psychopsia




msg:3087760
 7:10 pm on Sep 18, 2006 (gmt 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.

pixeltierra




msg:3087777
 7:28 pm on Sep 18, 2006 (gmt 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?

henry0




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

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




msg:3088232
 4:47 am on Sep 19, 2006 (gmt 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.

jatar_k




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

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

FalseDawn




msg:3088350
 8:04 am on Sep 19, 2006 (gmt 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!

pixeltierra




msg:3088948
 5:33 pm on Sep 19, 2006 (gmt 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...

Moosetick




msg:3090496
 5:59 pm on Sep 20, 2006 (gmt 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.

Vali




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

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

with an index on field1,field2,field3,etc

pixeltierra




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

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

Vali




msg:3100006
 4:06 am on Sep 28, 2006 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved