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 7 of 7
  1. #1
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post

    joins not helping

    i have this query and have tried changing it to left join and it goes from taking 1.68 seconds to 2.73 seconds....

    Code:
    SELECT a.id,a.gender,a.last_activity,a.photo_url,a.subscription,a.username,a.orientation,a.city,a.state,a.zipcode,a.country,b.status FROM `accounts` a, `profiles` b WHERE b.account_id = a.id AND b.status != '##private##' ORDER BY a.photo_url DESC, a.username ASC LIMIT 0,40
    using left join
    Code:
    SELECT a.id,a.gender,a.last_activity,a.photo_url,a.subscription,a.username,a.orientation,a.city,a.state,a.zipcode,a.country,b.status FROM `accounts` a LEFT JOIN `profiles` b ON b.account_id = a.id AND b.status != '##private##' ORDER BY a.photo_url DESC, a.username ASC LIMIT 0,40
    how can i optimize this query? there are 165,000 (give or take a few) rows in both tables

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,189
    Thanks
    80
    Thanked 4,560 Times in 4,524 Posts
    First, help us help you by making your code readable:
    Code:
    SELECT a.id,a.gender,a.last_activity,a.photo_url,a.subscription,a.username,
           a.orientation,a.city,a.state,a.zipcode,a.country,b.status 
    FROM `accounts` a LEFT JOIN `profiles` b 
    ON b.account_id = a.id AND b.status != '##private##' 
    ORDER BY a.photo_url DESC, a.username ASC 
    LIMIT 0,40
    Offhand, I'd say the only thing you can do (if you haven't done it already) is make sure that all the fields mentioned in your JOIN condition are indexed.

    I would assume the accounts.id is your primary key in that table?

    So did you index profiles.account_id and profiles.status?

    You might consider creating a single composite index for those two fields. Probably more efficient than two separate indexes.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post
    yes they are indexes.
    i also just tried moving `status` to the `accounts` table and getting rid of the join altogether. it still is quite slow (0.8 seconds) for only returning 40 rows... im not sure why

    Code:
    SELECT id,gender,last_activity,photo_url,
    subscription,username,orientation,city,state,zipcode,country,status FROM `accounts`
    WHERE status != '##private##' ORDER BY photo_url DESC, username ASC LIMIT 0,40

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,189
    Thanks
    80
    Thanked 4,560 Times in 4,524 Posts
    Because it has to *FIRST* find *ALL* the records that match the WHERE clause (or ON for the JOIN), *THEN* it has to SORT all those records...by *TWO* fields. Only then can it choose the first 40 of those records.

    In other words, the time to get 40 records and the time to get, say, 200 records is going to be essentially identical if your WHERE/ON causes it to find, say, 10000 records.

    Do this query and tell me what you get:
    Code:
    SELECT COUNT(*)
    FROM `accounts` a LEFT JOIN `profiles` b 
    ON b.account_id = a.id AND b.status != '##private##'
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post
    that still took 1.04 seconds without ORDER BY or LIMIT

  • #6
    Regular Coder
    Join Date
    Oct 2003
    Posts
    603
    Thanks
    2
    Thanked 1 Time in 1 Post
    actually i already moved status to accounts table so im not longer looking for a solution for joins... im trying to figure out why this query is so darned slow. it's not like theres millions of rows in the table, it's making my pages load really slowly.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,189
    Thanks
    80
    Thanked 4,560 Times in 4,524 Posts
    Same question. What is the NUMBER you get for the COUNT in this query:
    Code:
    SELECT COUNT(*)
    FROM `accounts`
    WHERE status != '##private##'
    Not how long does that take. What's the COUNT?

    Though if just the COUNT(*) indeed took 1.04 seconds without ORDER BY or LIMIT, I think you have your answer. You are simply getting so many records that, when you add in the ORDER BY, indeed that's how long it's going to take. The LIMIT probably has nearly no effect on the time (well...if you didn't have it there the time would be *MUCH* longer, just because MySQL has to transfer that much data from the server to the client...but that's all data transfer time...the actual query time is likely almost unaffected by LIMIT).
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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