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 9 of 9
  1. #1
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Left join select speed problem

    I am trying this:

    PHP Code:
    SELECT st.* FROM statuses AS st ORDER BY id DESC LIMIT 10 .. works gr8 ..0,015s...and the result is
    3556 nick1 status... ...... ......... .......
    3555 nick2 status...
    3554 nick3 status... ...... ...... ...
    3553 nick4 status...
    3552 nick5 status...
    3551 nick6 status...
    3550 nick7 status...
    .
    .
    .

    BUT! when i am trying to add next collum here via LEFT JOIN select...

    PHP Code:
    SELECT st.*,sl.typ FROM statuses AS st
    LEFT JO status_likes 
    AS sl
        ON sl
    .status_id=st.id

     WHERE st
    .nick='Trki' ORDER BY st.id DESC LIMIT 10IN 
    it takes at least 6seconds so verry bad and the result looks bad like this:

    3398 status1
    3342 status2 the same
    3342 status2 the same
    3342 status2 the same
    3342 ....
    3342
    3342
    3342
    .

    so .. u can see something is wrong :P What's going bad?

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    If its taking a long time to execute, that is an indication of incorrect indexes being set.
    So status_linkes.status_id and statuses.id must both be the same datatype and have the same definition, and then make sure both are indexed for faster lookups. You'll never get the same performance as just a single table query, but you shouldn't see a substantial increase either. Also make sure that the nick field on statuses is indexed, but given the 'nick' name for it, that would indicate to me that it's already likely indexed as a unique (but no guarantee since the table name itself doesn't tell me that it has to be unique).

    As for the output, that is what you'll get. The language is responsible for formatting it in another logical way. MySQL has a group_concat function that lets you join multiple child records into a single field, but I personally think that's among one of the sillier functions available.

  • #3
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts
    hm... how would you write in mysql this:

    Ah i am so messed up from it.

    I need select * from statuses LIMIT 30 ... then make a select from status_likes (on one status can be many likes) ...AND SELECT XY likes from status_likes .... where the status_likes.status_id will be the statuses.id ... so

    - select 30 statuses
    - select XY status likes on this 30 status id.

    ? :P Everyone who would help i would be so happy !

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Show us your COMPLETE table schemas. That is, all the fields and their types for both tables. And if fields are related to one another, show that as well.
    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
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Okay ... here are my tables what i need ( i changed my problem so please forget on the previous (its similiar) but lets look at this problem ... how would u solved it? :P )

    I have array with this ID of statuses (3398,3342,3129,2895,2157,2072,2062,1995,1945,1915) so i think we can use IN later...

    Now i need withdraw all comments from comments table where comments.status_id are from this array with ids...

    Then i need select from another table (where the users are) the profile_picture_ultra_small... and connect it with the comments nick names

    what is important... its there can be a lot of statuses to one status id ... and just one picture to the nick of person who have commented on. So The tables looks like:

    Users .. ( big but.. we need just one collum)



    Comments table:



    Any ideas? I have solution but is ultra slow....


    I wrote hardly something like this

    PHP Code:
    SELECT c.*,tu.profile_picture_ultra_small
     FROM comments 
    AS c
     LEFT JOIN users 
    AS tu
      ON c
    .nick tu.nick
     WHERE status_id IN 
    (3398,3342,3129,2895,2157,2072,2062,1995,1945,1915
    is that ok? it seems to be ... but .. hm
    Last edited by Trki; 11-16-2012 at 03:31 PM.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    UGH! VERY VERY BAD!

    WHY are you using nick to join the two tables????

    For that matter, WHY are you using nick in the COMMENTS table *AT ALL*!!!

    You should REPLACE the nick field in COMMENTS with user_id and make it a FOREIGN KEY to the id field in USERS table.

    But anyway, the reason it is so slow is that nick is NOT AN INDEX in your COMMENTS table!!!
    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.

  • #7
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    UGH! VERY VERY BAD!

    WHY are you using nick to join the two tables????

    For that matter, WHY are you using nick in the COMMENTS table *AT ALL*!!!

    You should REPLACE the nick field in COMMENTS with user_id and make it a FOREIGN KEY to the id field in USERS table.

    But anyway, the reason it is so slow is that nick is NOT AN INDEX in your COMMENTS table!!!
    hm... is that so problem ? with the nick... ? :/ btw

    but i have index on comments.status_id and i am using it here

    WHERE c.status_id IN (3398,3342,3129,2895,2157,2072,2062,1995,1945,1915)

    or its useless? rather on nick ... or can i use it on both?

    BTW the speed now :

    Found rows: 53 Warnings: 0 Duration for 1 query: 0.015 sec. */

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    I don't think you will get much faster than 15 milliseconds.

    As to whether your index of status_id is important or not: How can I tell?

    Run EXPLAIN on your query an MySQL will tell you.
    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.

  • #9
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    I don't think you will get much faster than 15 milliseconds.

    As to whether your index of status_id is important or not: How can I tell?

    Run EXPLAIN on your query an MySQL will tell you.
    Hm.. so you think its ok?

    btw i have runed explain and the result is



    WHat it actually means? :P


  •  

    Posting Permissions

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