GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Need to optimize mysql query

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

  • Need to optimize mysql query

    Hi,

    This select takes more than 4 secs to execute. Needs to be much faster. I am kind of stuck...

    Could anyone help me out?

    $query = "
    SELECT STRAIGHT_JOIN SQL_CALC_FOUND_ROWS
    p.id AS id
    ,p.rating
    ,DATE_FORMAT(p.added, '" . DB_DATE_FORMAT . "') AS added
    ,p.family
    ,p.designer_id
    ,p.vendor_id
    ,p.user_id
    ,p.name
    ,p.os
    ,p.type
    ,p.license
    ,p.image_name
    ,p.meta
    ,cp.category_id AS category_id
    ,c.parent_id AS parent_id
    ,ac.name AS parent_name
    ,c.name AS category_name
    ,COUNT(co.id) AS comments
    ,d.display AS designer_name
    ,v.display AS vendor_name
    FROM products AS p
    JOIN (categories_products AS cp, categories AS c, categories AS ac)
    ON (cp.product_id = p.id AND c.id = cp.category_id AND ac.id = c.parent_id)
    LEFT JOIN (comments AS co, designers AS d, vendors AS v)
    ON (co.id = p.id OR d.id = p.designer_id OR v.id = p.vendor_id)
    GROUP BY id
    ORDER BY added DESC
    $limit
    $offset
    ";

    Explain attached.

    Thanks
    Paul

  • #2
    Is it any faster if you run it without SQL_CALC_FOUND_ROWS?

    If yes - you would probably find a solution to your problem here:
    http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_f ound_rows-or-not-to-sql_calc_found_rows/

    Comment


    • #3
      Hi,

      It's better 3.36sec vs 4.07

      I would be great if I could drop temporary/filesort use somehow...

      Any help appreciated.

      Paul

      Comment


      • #4
        What happens if you remove STRAIGHT_JOIN?

        Whats the value of $limit?

        Comment


        • #5
          Hi,

          When I remove STRAIGHT_JOIN the query takes like 10secs.

          Limit = 5
          Offset value is empty

          Comment


          • #6
            I'm afraid it will not be easy to optimize it further. You will have to restructure your query.

            The reason why the query is slow is that your many joins results in a result set with around 21000 rows (according to the explain). This alone takes time. Make sure you have indexes on all foreign keys used to accomplish the join - this might make the original query faster.

            After this mysql must do a group by id on this result set - also a rather expensive task (when performed on that many rows). And last it does a filesort on the grouped result set.

            I would suggest you try to break of the query in smaller ones (each query should touch fewer tables if possible). This would make it easier to take advantage of indexes.

            Comment


            • #7
              categories_products has like 41k results already...each product can be assigned to multiple categories..so it's likely this table will grow faster than the actual products table.

              I know.....already playing with it like the 3rd day.

              I don't think I'm experienced enough to do that myself.

              Know anyone who would be willing to help with it for some $?

              Comment


              • #8
                vanuatu wrote on Tue, 28 October 2008 21:33

                categories_products has like 41k results already...each product can be assigned to multiple categories..so it's likely this table will grow faster than the actual products table.

                I know.....already playing with it like the 3rd day.

                I don't think I'm experienced enough to do that myself.

                Know anyone who would be willing to help with it for some $?

                Checkout percona.com.

                Comment


                • #9
                  Did it )

                  Had to rebuild it as you said. I dropped joins, used subqueries...etc

                  Takes 0.00 to complete )

                  Need to use 2nd query for row count, but as it uses index, takes 0.01 ... awesome.

                  Thanks for help!

                  Comment

                  Working...
                  X