On Mar 14, 2005, at 12:45 PM, Richard Lynch wrote:
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.
I see. What is the best way to run this type of query then?
--
Kurt Yoder http://yoderhome.com
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
