Announcement

Announcement Module
Collapse
No announcement yet.

ORDER BY slowing down query

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • ORDER BY slowing down query

    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?

  • #2
    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

    Comment


    • #3
      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 )

      Comment


      • #4
        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_viewe d) 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.

        Comment


        • #5
          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.

          Comment


          • #6
            Okay my coworker was able to run explain...

            EXPLAIN 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_viewe d) 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)

            AND
            (person_name_first LIKE '%%%' OR person_name_last LIKE '%%%' OR CONCAT(person_name_first,' ',person_name_last) LIKE '%%%')
            ORDER BY customer.cust_lead_date DESC, f_name ASC
            LIMIT 0,16;

            1, 'SIMPLE', 'customer', 'ALL', 'cust_lookup', '', '', '', 562170, 'Using temporary; Using filesort'

            1, 'SIMPLE', 'customer__property', 'ref', 'cust_prop_cust_id', 'cust_prop_cust_id', '4', 'f_agent.customer.cust_id', 1, 'Using index'
            1, 'SIMPLE', 'property', 'eq_ref', 'PRIMARY,prop_id', 'PRIMARY', '4', 'f_agent.customer__property.cust_prop_prop_id', 1, 'Using index'

            1, 'SIMPLE', 'customer__group', 'ref', 'cust_group_group_id', 'cust_group_group_id', '8', 'const,f_agent.customer.cust_id', 12, ''
            1, 'SIMPLE', 'customer__user', 'ref', 'cust_user_cust_id', 'cust_user_cust_id', '9', 'f_agent.customer.cust_id,const', 18, 'Using where'

            1, 'SIMPLE', 'person', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'f_agent.customer.cust_person_id', 1, 'Using where'

            Comment


            • #7
              Here are the others:

              EXPLAIN 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_viewe d) 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)
              LIMIT 0,16;

              1, 'SIMPLE', 'customer', 'ALL', 'cust_lookup', '', '', '', 562173, 'Using temporary'
              1, 'SIMPLE', 'customer__property', 'ref', 'cust_prop_cust_id', 'cust_prop_cust_id', '4', 'f_agent.customer.cust_id', 1, 'Using index'

              1, 'SIMPLE', 'property', 'eq_ref', 'PRIMARY,prop_id', 'PRIMARY', '4', 'f_agent.customer__property.cust_prop_prop_id', 1, 'Using index'
              1, 'SIMPLE', 'customer__group', 'ref', 'cust_group_group_id', 'cust_group_group_id', '8', 'const,f_agent.customer.cust_id', 12, ''

              1, 'SIMPLE', 'customer__user', 'ref', 'cust_user_cust_id', 'cust_user_cust_id', '9', 'f_agent.customer.cust_id,const', 18, 'Using where'
              1, 'SIMPLE', 'person', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'f_agent.customer.cust_person_id', 1, 'Using where'

              Comment


              • #8
                Interesting, explains are similar besides ORDER BY, which however should not make that difference as thee is group by (distinct) before it.

                Could be explain is misleading in this case.

                I would also suggest you to profile query by runnin at Handler_xxx increments during statement execution this is good way to check what was really happening.

                Comment


                • #9
                  the problem may be in the tables without primary key.
                  check if there are repeating rows in those tables.

                  Comment


                  • #10
                    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.

                    Comment

                    Working...
                    X