mysql - orderby and left outer join issue

Question

When I use left outer join with order by in the below query the query executes infinitely without giving a resultset. However - individually they work fine that is if I change it from left outer join to inner join. But I need all the values from interregen table below.

Not understanding what could possibly be the error :(

My mysql query:

   SELECT r.name, 
          r.network, 
          r.namestring, 
          i.name, 
          i.description, 
          r.rid,   
          i.id, 
          d.dtime,
          d.ifInOctets, 
          d.ifOutOctets, 
          FROM_UNIXTIME(d.dtime)
     FROM range AS r 
     JOIN interregen AS i ON r.rid = i.rid  
LEFT JOIN 1278993600_1_60 AS d ON i.id = d.id  -- i.id is the primary key
                              AND dtime BETWEEN 1279026000 AND 1279040400 
    WHERE r.net = 'ITPN'
      AND i.status = 'active' 
 ORDER BY i.id, d.dtime

Please help!! Thanks

Answer 1

It is probable that your problem is there are a very large number of rows in your interregen table that do not have a matching entry in table 1278993600_1_60. So the problem with the left outer join is that the query is trying to get a very much larger result set than with the inner join. It probably will return, eventually, but depending on the number of rows, it could take awhile.

Another look at the query and you might be able to improve performance if you move this:

AND dtime BETWEEN 1279026000 AND  1279040400

to somewhere AFTER the WHERE.