Slow query... sometimes

[sad69]

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:

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


The issue lies in including the u.user_fname column in the WHERE clause:

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


What really gets me is that I can search by anything and it's super quick, for example:

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..

Thanks,
Sadiq.

[raf]

i think it's quite obvious why this query can be slow...

i never needed anything like it, and it looks strange to me that you need such a complex query with so many outherjoisn and even a subselect in the from-clause, to just get an applicationID for that user.

so i think the inevitable 'are ou sure your db-design can't be optimised for this query' question needs to be asked.

[sad69]

Heh, sorry raf, but I need a LOT more than just the application_id! I had omitted those items from the select statement attempting to clean it up.

These are some more of the items I'm grabbing from the select:

SELECT
a.funding_date,
a.application_id,
a.date,
a.signing_datetime,
u.user_fname agent,
ap.home_phone,
p.street_number,
s.l_name,
n.followup_date n_fud,
n.user_fname follower_up_fname,
n.user_lname follower_up_lname


The rest of the query is above. I've updated the tables above to reflect the selected items.

Thanks for your help,
Sadiq.

[Kiwi]

Your basic problem is a LIKE clause. This is always slow and I think, with the way your query is structured, it will end up running it recursively (ie re-running it for every join; it may just re-run for every Application ID). Either way, the performance hit is going to be exponential.

I'm with Raf: I can't see a lot of point in dumping all of this stuff out in a single result set. Most of the complex joins can be run as seperate queries and combined, as necessary, at the application level. In fact, most of the left joins don't need to be explicit joins, and could simply be included in the where clause - but that shouldn't effect performance, just readability. Ultimately, this looks more like an issue of application design than SQL optimisation.

Beyond that, double checking that you've got all the fields properly indexed is quite important. Making sure that all your criteria are based on numerical data will make a difference. Beyond that, I think it's a question of application design rather than anything else.

[sad69]

I sort of see what you're saying, and I appreciate the response, but I'm still confused!

See there's just a textbox. The contents of it is the search criteria. This is supposed to display a series of rows so that the users can determine which record they want to look at.

It almost seems to me (not that I know a whole lot about this stuff..) that this needs to be in one query as I'm wanting all the records that match this search criteria.

In regards to your comparison of LEFT JOINs to putting that stuff in the WHERE clause, I've tried both, and the LEFT JOINs are like 10-20 times faster than putting those things in the WHERE clause. That's how I sped up this query in the first place. But now I'm stuck here, and I'm crying for help!

I know I'm doing something wrong, I'd just like a little more specific help as to what to do.

Again, thanks for your help!
Sadiq.

[raf]

You are not going to be able to solve this with some queryoptimizations.

You heva basically 3 options:
- buy a bigger server
- do some db-redesign --> probably some db denormalisation to have (part) of your results already in one table. Some data-redundancy, but if this reaaaaaly is a crititcal query, then performance can outweight maintenance-issues
- do some application-redesign. Cutting the proces up into a few smaller steps.

Or a combination of those...
I think this
Beyond that, double checking that you've got all the fields properly indexed is quite important. Making sure that all your criteria are based on numerical data will make a difference. Beyond that, I think it's a question of application design rather than anything else.holds a lott of truth.

I think your current situation is:
- the user types in a keyword in a textfield and you then use that inside the querys like-clause
- the query is ran
- all data is displayed

What i would do is: have the user select the username so that i can send the user_id to the server and use that inside a where-clause. This user_id should be your usertables primary key (numeric, indexed).
So instead of the searchfield, i'd have a alfabetic list (A - Z) and when you click on a letter, i'd display all users which name starts with that letter (as a link with the user_id in the querystring.). or whatever other setup to get to the user_id.
Then, instead of displaying all this data, i'd probably load a page with all applications or whatever for that user. Again with the application_id in the querystring etc.
You see? a gradual drilldown, always using the PK's to go one level deeper, folowing your db-design so that you don't need to query more then 2 or 3 tables at once. so that you can always use 'inner join's and don't need subquerys.
If you have good navigation, then most users will prefer such a drildown-approachwith fast loading pages, instead of a slow generated page that immedeately throws all info at them.

just the way i do it ...

[sad69]

I've got all the indexing setup correctly as suggested.

We do have a bigger server lined up, and right now the application is just being held on my computer in its pre-alpha stages.

I appreciate your input, but the gradual breakdown is just not an option at this time.. but perhaps in the future once I develop various 'views' (specific to users logging in..).

It just baffles me that this is the only field that causes issues. I can search by any other field and it works just fine, it's just u.user_fname...

Not a big deal at this time. If it's still an issue with the new server then I'm going to have to do some serious re-evaluating!

Thanks again!
Sadiq.

[sad69]

LOL, so I thought I had the indexing setup correctly... I'd been doing some reading on how to optimize MySQL, and one thing I read about was using EXPLAIN in front of a query.

So I used EXPLAIN and found a bunch of NULLs all over the place where I believed I should have seen various column names. Adding those indexes has immensely sped up my queries.

raf, thanks again for your help, I apologize for my ignorance with regard to indexes. My boss is happy, so I am too!

I just thought I'd mention my use of EXPLAIN, as it should prove to be useful. You may think you know what MySQL is doing, but using EXPLAIN you will know what MySQL is doing, and that can be a big help in understanding how to optimize your queries.

Sadiq.