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 2 of 2
  1. #1
    Regular Coder
    Join Date
    Jun 2009
    Posts
    146
    Thanks
    3
    Thanked 21 Times in 21 Posts

    Subquery Count slows query right down

    I need to write a better query, as this one takes 9 seconds to run! Without the subquery it take 0.2 seconds, which is fantastic, but with the count of actual products ordered it slows right down. Clearly my logic could do with some improving!

    SELECT D.id_item, D.sku, DATE_FORMAT(D.date,'%d-%m-%Y') AS date, D.title, D.cost, D.week, D.publish_date, D.expire_date, D.quantity_left, D.archived, A.full_name, B.cat_name,

    PHP Code:
    (SELECT COUNT(*) FROM ac_register_items AS ZZ INNER JOIN ac_register AS YY ON ZZ.order_id YY.order_id WHERE ZZ.item_id D.id_item AND YY.archived != '1' AND YY.pay_status != '1') AS booked 


    FROM ac_description 
    AS 

    LEFT JOIN ac_avaliable 
    AS A ON A.hp_location=D.hp_location 
    LEFT JOIN ac_avaliable_category 
    AS B ON A.category B.cat_id 
    WHERE D
    .xt_edits='0' 
    ORDER BY D.date 
    DESC LIMIT 0
    100 
    Is there a smarter way to count these?

  • #2
    Regular Coder
    Join Date
    Jun 2009
    Posts
    146
    Thanks
    3
    Thanked 21 Times in 21 Posts
    I am getting there, have realized the subquery in itself is not slow, its the whole running the subquery for every top result, say 100. Have moved it into table joins, and getting much better responses from the DB.


  •  

    Posting Permissions

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