#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    3
    Rep Power
    0

    Arrow Subquery slows down execution when no rows returned...


    Hey guys,

    I have the following, which performs nicely when the subquery returns records:

    Code:
    SELECT * 
    FROM  `mydb`.`dataload` sb
    WHERE sb.`domain` 
    IN (
    SELECT dm.`domain`
    FROM  `mydb`.`domain` dm
    INNER JOIN  `mydb`.`set` st ON dm.`set_id` = st.`set_id` 
    WHERE st.set_id = 6
    )
    LIMIT 2000
    However, when the subquery IN (... Subquery ...) returns no rows, it takes ages to execute. The subquery, when returning rows, is not expected to give more than 25 values. The master query selects from the dataload table, which contains 40M records...

    Any thoughts?

    THANKS!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    try it like this --
    Code:
    SELECT sb.* 
      FROM mydb.set AS st
    INNER
      JOIN mydb.domain AS dm
        ON dm.set_id = st.set_id 
    INNER
      JOIN mydb.dataload AS sb 
        ON sb.domain = dm.domain
     WHERE st.set_id = 6
    LIMIT 2000

    Comments on this post

    • ETbo agrees : Thanks r937! Tt works perfectly.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    3
    Rep Power
    0

    Arrow Deduplicate 'smart' random row query?


    Wrong Post... Apologies...

IMN logo majestic logo threadwatch logo seochat tools logo