Posted by Richard Lynch on 03/14/05 19:45

>> Select blablabla FROM t1,t2,t3
>> WHERE customers LIKE '%$s%'
>> OR name LIKE '%$s%'
>> OR domain LIKE '%$s%'
>> OR email LIKE '%$s%'
>> OR log LIKE '%$s%'
>> AND t1.id = t2.t1_id
>> AND t1.id = t3.t1_id
>>
>> Horror!
>>
>
> Perhaps I'm missing something, but what's wrong with that query? It
> looks like a pretty normal search query to me. If you need to search
> through a bunch of records for various text fragments, and you're not
> certain which field they're in, why *wouldn't* you use a query like
> this?

Been there. Done that.

Because of operator precedence, this turns into:

customers like '%$s%' or name like '%$s%' or domain like '%$s%' or email
like '%$s%' or (log like '%$s%' and t1.id = t2.t1_id and t1.id = t3.t1_id)

So you basically end up with the cartesian product of three tables for
most of the tests, and the foreign keys only kick in for the log test.

Assuming the three tables have reasonable amounts of data in them, you
then are checking count(t1) * count(t2) * count(t3) tuples with LIKE on
text fields.

This should quickly bring the database server to its knees. :-)

I'd be surprised if this kind of thing survived even the worst QA -- You'd
have to have hefty db server, and very small tables and VERY BAD QA to not
notice something was wrong.

--
Like Music?
http://l-i-e.com/artists.htm

[Back to original message]