Hello all, I am stuck on the best way to proceed in order to make a SQL query efficient and quick searching through a very large (near million and growing) database. I am working with MySQL 3.23.58 and using PHP to construct a dynamic query, based on HTML form input. Please let me know if you need more information, or would like to see the code which constructs the SQL statement.
My form fields: Skills Last Name First Name City State Tables: candidate resume Candidate fields: Candidate_ID Last_Name First_Name Location <..snip..> Resume fields: Candidate_ID Section_ID Section_Value ------- I did not create this database, and changing it is really not an option. ------- Within PHP, I construct a dynamic query, using only those fields that the user has submitted in the form. Example, where SKILLS and STATE have been filled in: SELECT resume.Section_Value, candidate.Location FROM candidates, resumes WHERE resume.Section_Value CONTAINS 'html' OR 'unix' AND resume.Section_ID = '1' AND candidate.Location LIKE '%CA%' OR '%California%' (note: this is necessary due to the way the original table was created. Location information is in one text field, with some entries entered as 'CA' and some as 'California') Or where Location only is selected: SELECT candidate.Location FROM candidate WHERE camdodate.Location LIKE '%CA%' OR '%California%' I have thought of approaching this 2 ways. 1. Search first for candidates meeting every other criteria, ie. State, or Last Name. Then drill down on any results for matching skills. 2. Construct the full query based on all matches based on fields entered, and then matching results between tables with Candidate_ID for display. Of course, what if there IS no other criteria to drill down upon? Right now, the form requires that a state always be present. However, I will need to build in the ability to search for anyone with HTML and Unix skills in any state. So if 'skills' are the only fields submitted, the dynamic query would look like: SELECT resume.Section_Value FROM resume WHERE resume.Section_Value CONTAINS 'html' OR 'unix' AND resume.Section_ID = '1' (resume.Section_ID is the section for skills) It would entail searching through millions of records. Is there any way to make this faster and/or more efficient, based on the way I'm handling it? How long would this search take for so many records? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]