Hi Bill, Try putting first all the county equality tests together and then the other tests in the where clause.
select * from jobs where (county = 'county1',' or county = 'county2'... or description like '%county1%' or or description like '%county2' ... or title like '%county1%' or title like '%county2'....) . Since you are using perl the above query can be easily created. ie if user just selects county1 the query will be select * from jobs where (county = 'county1' or description like '%county1%' or or title like '%county1%' ) . if count1 and count3 are selected it becomes select * from jobs where (county = 'county1' or county = 'county3' or description like '%county1%' or description like '%county3' or title like '%county1%' or title like '%county3') . if no field selected by the user then just select * from jobs; These queries should run faster if you have index on county column. Since this column is small in size compared to the other two fields first searching on these should return the result faster. The description and title can be in any order in the where clause depending on the size of the fields and the probability of finding the string in these fields. If description column is smaller and there is higher probability of finding the string in this column then put this column in the where clause before the title column. If not, then first put the title and then the description. Tell us if there is any performance difference. Note that changing the query does not have any effect if user selects only one county. Anvar. At 03:45 PM 04/01/2002 -0500, you wrote: >Greetings all, >I'm new to the list, and I'm somewhat new to MySQL. >I have a somewhat simple question that I hope someone can help me with. > >I'm designing a database for a job search. There are a little over 10,000 >entries in the db. >I need to query the database to accomplish a few results, one of which is a >little perplexing to me. > >Just a little background as to how it works - >The person can choose a county and a basic job description, and I make the >query string dependant on what they choose. For example, if they choose one >specific county or select "all counties," it sets the query appropriately. >The problem that I have is - if there are 7 counties, I've got the string >saying: >select * from jobs where (county='county1' or description like '%county1%' >or title like '%county1%') or (county='county2' or description like >'%county2%' or title like '%county2%') .... etc, and if they choose a >specific keyword it then throws that into the query. > >The computer is a dual P3 1GZ w/1.5GB's of RAM - RedHat 7.2 - PERL DBI - it >takes over 3 seconds to complete queries like that, and I know that I can >get it faster. > >Any suggestions would be appreciated sincerely. >Thanks, >--Bill > > >--------------------------------------------------------------------- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
