MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Joshua Chamas Date:September 8 1999 1:27am
Subject:Re: How do you specify all records in a WHERE statement?
View as plain text  
a rancid amoeba wrote:
> 
> > You could do WHERE Region like '%', but for performance reasons,
> > you might be better off dropping the Region test entirely.
> >
> 
> Well, besides impacting the functionality of the interface, there's
> other reasons I can't just drop the yucky part. In the case that the
> user chooses to search the entire US (instead of a specific state in
> the US), then State shouldn't be limited. Also, this is a simplified
> example. The WHERE statement also includes Genre='$genre'. If the user
> picks a specfic genre, then there's no problem, but they may want to
> see all genres in which case Genre shouldn't be limited.
> 

What I mean is that dropping the part of the where clause not necessary
for the query instead of matching '%' will probably result in 
the best performance, unless MySQL optimizes out the like '%' test.

So if for all genre, your query is just:
  where region = ? and state = ? and country = ?

for all states:
  where region = ? and country = ? and genre = ?

etc... instead of 
  where state like '%' and region = ? and country = ? and genre = ?

In perl this kind of dynamic SQL is easy to contruct, as you just 
collect the search tests in an array:

# PSEUDO CODE
for(@inputs) {
	next if /ALL/;
	push(@tests, "$column = $match");
}
... then join with AND ...

$where_test = join(' AND ', @tests);

This is all a moot point if MySQL optimizes out the like '%'
test which would be very cool.  

-- Joshua
______________________________________________________________________
Joshua Chamas                      Chamas Enterprises Inc.
NODEWORKS - web link monitoring    Long Beach, CA  USA  1-562-432-2469
http://www.nodeworks.com           http://www.chamas.com
Thread
How do you specify all records in a WHERE statement?a rancid amoeba8 Sep
  • Re: How do you specify all records in a WHERE statement?Joshua Chamas8 Sep
    • Re: How do you specify all records in a WHERE statement?a rancid amoeba8 Sep
      • Re: How do you specify all records in a WHERE statement?Joshua Chamas8 Sep
        • Re: How do you specify all records in a WHERE statement?Michael Widenius8 Sep
      • Re: How do you specify all records in a WHERE statement?Jules Bean8 Sep
RE: How do you specify all records in a WHERE statement?Clinton Gormley8 Sep
  • Re: How do you specify all records in a WHERE statement?Martin Ramsch8 Sep
  • RE: How do you specify all records in a WHERE statement?Michael Widenius8 Sep