SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join order by performance

    Hi,
    Can an ORDER BY (query with 4 tables) cause performance troubles ?
    Is it better get rid of it or not ?
    Thanks in advance.

    Bye.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Why, are you having performance problems?

  3. #3
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    General error: 2006 MySQL server has gone away

  4. #4
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    SELECT `s`.`id` AS `sid`, `s`.`registration_datetime` AS `sregdate`, `s`.`comment` AS `scomment`, CONCAT(u.first_name,' ',u.last_name) AS `fullname`, `z`.`zip`, CONCAT(z.municipality,'(',z.province,')') AS `livein` 
    FROM `np_sign` AS `s` 
    LEFT JOIN `np_user` AS `u` ON s.user_id = u.id 
    LEFT JOIN `np_user_detail` AS `ud` ON u.id = ud.user_id 
    LEFT JOIN `np_enum_zip` AS `z` ON ud.zip_id = z.id 
    WHERE (s.petition_id = 1) AND (s.STATUS = 'confirmed') AND (u.STATUS = 'confirmed') 
    ORDER BY `s`.`id` DESC

  5. #5
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does the query perform well without the order by clause? What indexes do you have on the tables (I'm assuming s.id has a primary index on it)? Your order by isn't complicated so it most likely isn't the problem.
    MySQL v5.1.58
    PHP v5.3.6

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Do you have indexes on all join columns, and on the s.id column?

  7. #7
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @BrandonK
    yes is the PK.

    @guido2004
    s.id is the PK but the other columns are without index (ie s.comment)

  8. #8
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How does the query run without the order by? Heck, how well does it run with? How slow are we talking? How many records are you dealing with, etc. etc. We need more info to help I think
    MySQL v5.1.58
    PHP v5.3.6

  9. #9
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    with order by 63128 rows in 2:03.5940
    without order by 63128 rows in 2:00.4552

    All in all only a difference of 4 secs about.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    whisher, if you do a SHOW CREATE TABLE for each table, we will be able to see and evaluate the indexes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    whisher, if you do a SHOW CREATE TABLE for each table, we will be able to see and evaluate the indexes
    I know but I've just made the db schema so also without
    SHOW CREATE TABLE I know which field has an index.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by whisher View Post
    I know which field has an index.
    yeah, but we don't, so we can't help you any further
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lol

    np_sign
    PRIMARY KEY (`id`),
    KEY `user_id` (`user_id`),
    KEY `petition_id` (`petition_id`)

    np_user
    PRIMARY KEY (`id`),
    UNIQUE KEY `email` (`email`)

    np_user_detail
    PRIMARY KEY (`user_id`),
    KEY `education_id` (`education_id`),
    KEY `job_id` (`job_id`),
    KEY `zip_id` (`zip_id`),
    KEY `income_range_id` (`income_range_id`),
    KEY `political_belief_id` (`political_belief_id`),
    KEY `marital_status_id` (`marital_status_id`)

    np_enum_zip
    PRIMARY KEY (`id`),
    KEY `zip` (`zip`)

    Sorry but I can't show you all the sql stuff
    if not my top dog beat me up

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    your first join should really be an inner join, since you require users to be confirmed

    if every np_user row must have a matching np_user_detail row, then they should all be inner joins

    your indexes look okay, but of course the EXPLAIN will be the final clue as to whether the indexes are used properly

    at this point i would recommend you throw away your ORDER BY clause because it doesn't look like it's important
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the points
    Bye.


Bookmarks

Posting Permissions

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