Re: Re: mysql statement [SOLVED]

Couple of little pointers. If you're doing the sub-select, then you don't need the "group like 'mac'" because you've already limited your query in the subselect to a specific groupname. The subselect is probably unnecessary since what you're doing is relatively simple and uses the same table. It probably just adds overhead. But in relation to that, you need to segregate your OR statements from your final AND statement or it won't limit by group properly. WHERE ( ordernum LIKE "35132" OR price LIKE "35132" OR partnum LIKE "35132" OR vendor LIKE "35132" OR purpose LIKE "35132" OR tracking LIKE "35132" OR contact LIKE "35132" ) AND group LIKE 'mac' Notice the ( and ). Next pointer.. LIKE is used when you're doing a non-exact search, but you haven't used any wildcards to indicate a partial search. So what you're essentially doing is the same as "price = 35132". If you're doing a multi-field search and want to use LIKE, you'd do this on each: price LIKE "%35132%" The % is sorta like * in other systems. Any quantity of any characters can match that space. Or you can do price LIKE "35132%" if you want to search the beginning of the field (note the % at the end, but not the beginning of the search string this time). One last thing... I don't know if it increases speed or not, but since you're using so many fields this MAY speed up the query. Depends on how much data you're searching through and if you're doing %search% or need to find start/end strings. Anyway, here's the test.. what's faster, what you're trying to do: WHERE ( ordernum LIKE "35132" OR price LIKE "35132" OR partnum LIKE "35132" OR vendor LIKE "35132" OR purpose LIKE "35132" OR tracking LIKE "35132" OR contact LIKE "35132" ) AND group LIKE 'mac' or something like this.... WHERE CONCAT(ordernum, price, partnum, vendor, purpose, tracking, contact) LIKE "%35132%" AND group LIKE '%mac%' All the LIKE comparisons against a ton of data MAY be more taxing on the server than doing a CONCAT of all the fields then doing a single LIKE. If you're doing LIKE %search% where it can appear anywhere in any of the fields, then CONCAT + LIKE would work just as good as LIKE OR LIKE OR ... results-wise. I don't know if it's faster/less intensive or not though. You'd have to do some tests. Also, comparisons like "=" should (if I recall) be faster than LIKE comparisons. So if you really meant to use "=", do that instead. Let me know if any of that's unclear. I know I get some kooky ideas sometimes. Also, anyone see anything I screwed up or have thoughts on this matter? Good luck! -TG ----- Original Message ----- From: Jas <jason.gerfen@scl.utah.edu> To: php-db@lists.php.net Date: Wed, 26 Sep 2007 12:08:53 -0600 Subject: [PHP-DB] Re: mysql statement [SOLVED] > Got if figured out, needed a sub-select type of query: > > mysql> SELECT * > -> FROM ( SELECT * FROM orders > -> WHERE group = "groupname" ) > -> AS orders UNION SELECT * FROM orders > -> WHERE ordernum LIKE "35132" > -> OR price LIKE "35132" > -> OR partnum LIKE "35132" > -> OR vendor LIKE "35132" > -> OR purpose LIKE "35132" > -> OR tracking LIKE "35132" > -> OR contact LIKE "35132" > -> AND group LIKE 'mac' > -> ORDER BY ordernum > -> LIMIT 0 , 30;