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]
