Not the answer you need?
Register and ask your own question!

ORDER BY slowing down query

krisc13krisc13 EntrantInactive User Role Beginner
I have a large select statement that runs fine until the ORDER BY clause is added. The order by is crucial but makes the page take over 10 minutes to load and it never finishes. May I paste the statement here and perhaps get some advice?

Comments

  • toastytoasty Contributor Inactive User Role Beginner
    I'm sure if you post the details here someone will help you.

    The performance/optimisation forums on the mysql site are usually a good place to post these kinds of things too.

    http://forums.mysql.com/list.php?24
    http://forums.mysql.com/list.php?115

    Toasty
  • PeterPeter Percona CEO Percona Moderator Role
    Right. Please send EXPLAIN with and without ORDER BY.

    It is easy to come up with query where order by would make it run 1000 times longer and more )
  • krisc13krisc13 Entrant Inactive User Role Beginner
    SELECT DISTINCT
    cust_id,
    cust_priority priority,
    person_name_first f_name,
    person_name_last l_name,
    person_email_primary email,
    person_address_a address,
    person_address_a2 address_2,
    person_address_a_city city,
    person_address_a_zip zip,
    person_address_a_state state,
    customer__group.cust_group_id group_id,
    customer__group.cust_group_shared_by shared_by,
    customer__user.cust_user_id owner_id,
    UNIX_TIMESTAMP(cust_lead_date) lead_date,
    UNIX_TIMESTAMP(customer__user.cust_user_date_added) date_added,
    UNIX_TIMESTAMP(customer__user.cust_user_date_viewed) date_viewed,
    UNIX_TIMESTAMP(customer__user.cust_user_refer_date_in) date_refered_in,
    UNIX_TIMESTAMP(customer__user.cust_user_refer_date_out) date_refered_out,
    customer__user.cust_user_refered_by refered_by,
    customer__user.cust_user_refered_to refered_to,
    customer__user.cust_user_id cust_user
    FROM person
    JOIN customer
    ON cust_person_id = person_id
    LEFT JOIN customer__property
    ON cust_prop_cust_id = cust_id
    LEFT JOIN property
    ON prop_id = cust_prop_prop_id
    LEFT JOIN customer__group
    ON cust_group_cust_id = cust_id
    AND cust_group_group_id = 1
    LEFT JOIN customer__user
    ON cust_user_cust_id = cust_id
    AND cust_user_user_id = 46
    WHERE
    (customer__group.cust_group_id IS NOT NULL OR customer__user.cust_user_id IS NOT NULL)
    ORDER BY customer.cust_lead_date DESC, f_name ASC LIMIT 0,16.

    Explain times out when we run it on this query. When we run this query without the ORDER BY it runs very fast.
  • PeterPeter Percona CEO Percona Moderator Role
    I can't tell you much without EXPLAIN.

    What do you mean by "times out" - run it from standard mysql command line client it should not be that long and it should not time out even if EXPLAIN would get hours to run.
  • migandhimigandhi Entrant Current User Role Beginner
    the problem may be in the tables without primary key.
    check if there are repeating rows in those tables.
  • sterinsterin Mentor Inactive User Role Contributor
    How many MB is the result set from this query?

    And what is your sort_buffer_size set to?

    Usually when an order by takes a really long time the sort_buffer_size is to small and that forces mysql to write the temporary table to disk which slows down things a _lot_.

    Since you have LEFT JOIN and you are ordering on two columns that are not in the same table I don't think that we can avoid the temporary table for the order by in the end.
    The only thing we can do is to increase the buffer size to perform the sort faster.

    The other possibility you have is if you can narrow down the search criteria in the conditions so that there are fewer rows to sort.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.