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

Thread: Slow query.

  1. #1
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts

    Slow query.

    I have the job of combing the databases from lots of smaller sites (all with same database structure) in to one big central database. This was going fine until i came across the first query that is really slow.

    Code:
    SELECT * , RANGE_ID AS ID
    FROM SITES_RANGE
    WHERE SITE_ID =00001
    AND RANGE_ID
    IN (
    
    SELECT DISTINCT RANGE_ID
    FROM SITES_PRODUCT
    WHERE CATEGORY_ID = '00009'
    
    )
    ORDER BY ORDERING DESC
    While the sub query takes only 0.13 seconds. The whole query combined takes 3.5 seconds. Which is too slow i think. How can i speed it up. The query basically checks product to select all the ranges that belong to a certain category for that site. Can i add an index to my tables? And if so what fields should i add the indexes to?
    You can not say you know how to do something, until you can teach it to someone else.

  • #2
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    I tried using a join query instead of the sub query in the end

    Code:
    SELECT SITES_RANGE . * , SITES_RANGE.RANGE_ID AS ID
    FROM SITES_RANGE
    JOIN SITES_PRODUCT ON SITES_RANGE.RANGE_ID = SITES_PRODUCT.RANGE_ID
    AND SITES_PRODUCT.CATEGORY_ID = '00001'
    GROUP BY ID
    Speed was 0.11 secs

    Problem solved
    You can not say you know how to do something, until you can teach it to someone else.


  •  

    Tags for this Thread

    Posting Permissions

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