I'm having an issue with a search script I've created. At first I wasn't sure what the problem was, but now I've narrowed it down to ONE field in my WHERE clause.
This is a pretty huge query, by my standards anyway, (and it's tamed down; ie. I've cut out most of the selects and a couple of left joins, and there's no PHP in this posted version!) so don't bash me too much for that. I can't think of any other way to get/search by all this information.
These are the tables, if you require:
The issue lies in including the u.user_fname column in the WHERE clause:Code:application_table application_id funding_date date signing_datetime notes_table note_id application_id follower_up followup_date applicant_table applicant_id application_id spouse1_id spouse2_id home_phone spouse_table spouse_id l_name f_name property_table property_id street_number city users_table user_id user_fname user_lname
What really gets me is that I can search by anything and it's super quick, for example:Code:SELECT a.application_id FROM application_table a LEFT JOIN (SELECT application_id, followup_date, user_fname, user_lname FROM notes_table LEFT JOIN users_table ON follower_up=user_id WHERE followed_up="no" GROUP BY application_id ORDER BY followup_date ) n ON a.application_id=n.application_id LEFT JOIN applicant_table ap ON a.application_id=ap.application_id LEFT JOIN spouse_table s ON ap.spouse1_id=s.spouse_id or ap.spouse2_id=s.spouse_id LEFT JOIN property_table p ON p.application_id=a.application_id LEFT JOIN users_table u ON a.agent=u.user_id WHERE u.user_fname like '%brent%' GROUP BY a.application_id
- n.user_fname (follower_up from notes_table; it seems like this is the same query.. yet 8 times faster)
- s.l_name (spouse_table.l_name)
- p.city (property_table.city)
I would really appreciate it if someone can help me understand why this query is slow, and how to speed it up! If you need more info, just let me know and I'll tell you what I know..