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