List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 8 1999 12:54pm
Subject:Re: How do you specify all records in a WHERE statement?
View as plain text  
>>>>> "Joshua" == Joshua Chamas <joshua@stripped> writes:

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

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

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

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

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

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

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

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

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

Hi!

No, MySQL will not optimize out the LIKE '%' case (and one can't do
this in the case where the column may contain a NULL value).  On the
other hand the LIKE "%" will not use indexes and it's very fast...

Regards,
Monty
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