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