Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Senior Coder
    Join Date
    Feb 2004
    Posts
    1,206
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Slow query... sometimes

    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:
    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
    The issue lies in including the u.user_fname column in the WHERE clause:
    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
    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.
    Last edited by sad69; 08-31-2004 at 12:52 AM. Reason: updated table columns

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    Senior Coder
    Join Date
    Feb 2004
    Posts
    1,206
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:
    Code:
    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.
    Last edited by sad69; 08-31-2004 at 12:57 AM.

  • #4
    Regular Coder
    Join Date
    Oct 2002
    Posts
    380
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #5
    Senior Coder
    Join Date
    Feb 2004
    Posts
    1,206
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #6
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 ...
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #7
    Senior Coder
    Join Date
    Feb 2004
    Posts
    1,206
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #8
    Senior Coder
    Join Date
    Feb 2004
    Posts
    1,206
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •