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
    Jan 2005
    Location
    NY, USA
    Posts
    132
    Thanks
    1
    Thanked 1 Time in 1 Post

    ORDER BY slows down join considerably

    Hi,

    I'm doing a select to grab some rows, and I need to select the 100 newest ones, so I'm doing a sort on an integer timestamp field.

    Anyway, my query looks something like this (I've simplified it just to show the concept):
    Code:
    SELECT c.content_id, c.content_user_id, c.content_client_timestamp, u.user_username 
    FROM tbl_content as c, tbl_user_relationships as r, tbl_users as u
    WHERE r.relationship_user_id = VARIABLE_USER_ID_HERE && c.content_user_id = r.relationship_to_user_id && u.user_id = c.content_user_id 
    ORDER BY c.content_client_timestamp DESC LIMIT 100
    Now, the query works fine, but as far as I'm concerned, it's slow. With about 150,000 records in the content table, it takes about .4 seconds. By tweaking the indexes, I can get that time down a little, but that seems to effect other stuff. But, by removing the ORDER BY, it goes down to a very respectable 0.0013.

    My indexes are like this: I of course have all the fields that are joined on indexed, and I have content_client_timestamp indexed. It seems like it's not properly using that index to order by. I tried doing an index like content_client_timestamp, content_user_id, and that brings it down to 0.15 seconds, but it seems I need to force MySQL to use that index in place of just content_user_id which I find strange.

    Any insight into this issue is greatly appreciated. I can't tell if this is just a complex query that requires this amount of time, or I'm doing something wrong. I'm leaning towards it shouldn't be taking that long If you have any questions about the setup, please let me know.

    Thanks in advance.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You'll need to run an EXPLAIN on the query to see if your indexes are being used.

  • #3
    Regular Coder
    Join Date
    Jan 2005
    Location
    NY, USA
    Posts
    132
    Thanks
    1
    Thanked 1 Time in 1 Post
    When I run explain, and do tests, it seems to like the index with the timestamp first and then the content_user_id the best. With about 500,000 rows in the table, it takes about .03 seconds. I guess that's not bad. Does this seem reasonable?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,151
    Thanks
    80
    Thanked 4,557 Times in 4,521 Posts
    0.03 for selecting top 100 out of 500,000 rows? yes, that's quite reasonable.

    Could it be faster if MySQL were smarter? You know it.

    MySQL's use of indexes is...well, to be generous..."primitive", when compared to the professional DBs such as SQL Server and Oracle.
    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.

  • Users who have thanked Old Pedant for this post:

    cooleo100d (02-02-2011)

  • #5
    New Coder
    Join Date
    Feb 2011
    Posts
    48
    Thanks
    1
    Thanked 2 Times in 2 Posts
    SELECT c.content_id, c.content_user_id, c.content_client_timestamp, u.user_username
    FROM tbl_content as c, tbl_user_relationships as r, tbl_users as u
    WHERE r.relationship_user_id = VARIABLE_USER_ID_HERE && c.content_user_id = r.relationship_to_user_id && u.user_id = c.content_user_id
    ORDER BY c.content_client_timestamp DESC LIMIT 0, 100

    Instead of LIMIT 0, 100 you've put LIMIT 100, so every record was in the result set of your query except those you wanted to select.

  • #6
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Quote Originally Posted by DistantJob View Post
    SELECT c.content_id, c.content_user_id, c.content_client_timestamp, u.user_username
    FROM tbl_content as c, tbl_user_relationships as r, tbl_users as u
    WHERE r.relationship_user_id = VARIABLE_USER_ID_HERE && c.content_user_id = r.relationship_to_user_id && u.user_id = c.content_user_id
    ORDER BY c.content_client_timestamp DESC LIMIT 0, 100

    Instead of LIMIT 0, 100 you've put LIMIT 100, so every record was in the result set of your query except those you wanted to select.
    Huh? I don't think so.

    LIMIT 0, 100 is the same as LIMIT 100. If you only provide one argument to the LIMIT clause, it returns that many rows starting from the beginning of the result set.
    OracleGuy

  • #7
    Regular Coder
    Join Date
    Jan 2005
    Location
    NY, USA
    Posts
    132
    Thanks
    1
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Old Pedant View Post
    0.03 for selecting top 100 out of 500,000 rows? yes, that's quite reasonable.

    Could it be faster if MySQL were smarter? You know it.

    MySQL's use of indexes is...well, to be generous..."primitive", when compared to the professional DBs such as SQL Server and Oracle.
    OK, thanks for the info. Sounds good.

    Quote Originally Posted by oracleguy View Post
    Huh? I don't think so.

    LIMIT 0, 100 is the same as LIMIT 100. If you only provide one argument to the LIMIT clause, it returns that many rows starting from the beginning of the result set.
    Yeah, this is correct. The query is correct semantically, it just seems like it's a matter of tweaking the indexes.


  •  

    Tags for this Thread

    Posting Permissions

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