title: Is it bad practice to do "small_table LEFT JOIN large_table"?
body: <p>I'm connecting to a database which I cannot administer and I wrote a query which did a left join between two tables - one small and one several orders of magnitude larger. At some point, the database returned this error:</p>
  
  <blockquote>
    <p>Incorrect key file for table '/tmp/#sql_some_table.MYI'; try to repair it</p>
  </blockquote>
  
  <p>I contacted the administrators and I was told that I'm getting this error because I'm doing the left join incorrectly, that I should NEVER left join a small table to a large table and that I should reverse the join order. The reason they gave was that when done my way, MySQL will try to create a temp table which is too large and query will fail. Their solution fails elsewhere, but that's not important here.</p>
  
  <p>I found their explanation odd, so I ran explain on my query:</p>
  
  <pre><code>           id = '1'
    select_type = 'SIMPLE'
          table = 'small_table'
           type = 'ALL'
  possible_keys = NULL
            key = NULL
        key_len = NULL
            ref = NULL
           rows = '23'
          Extra = 'Using temporary; Using filesort'
  
             id = '1'
    select_type = 'SIMPLE'
          table = 'large_table'
           type = 'ref'
  possible_keys = 'ID,More'
            key = 'ID'
        key_len = '4'
            ref = 'their_db.small_table.ID'
           rows = '41983'
          Extra = NULL
  </code></pre>
  
  <p>(The 41983 rows in the second table are not very interesting to me, I just needed the latest record, which is why my query has <code>order by large_table.ValueDateTime desc limit 1</code> at the end.)</p>
  
  <p>I was careful enough to do a select by columns which the admins themselves told me, should hold unique values (and thus I assumed indexed), but it seems they haven't indexed those columns.</p>
  
  <p>My question is - is doing the join the way I did ('small_table LEFT JOIN large_table') bad practice in general, or can such queries be made to execute successfully with proper indexing?</p>
  
  <p>Edit:
  Here's what the query looks like (this is not the actual query, but similar):</p>
  
  <pre><code>select large_table.ValueDateTime as LastDate,
         small_table.DeviceIMEI as IMEI,
         small_table.Other_Columns as My_Names,
         large_table.Pwr as Voltage,
         large_table.Temp as Temperature
  from small_table left join large_table on small_table.ID = large_table.ID
  where DeviceIMEI = 500
  order by ValueDateTime desc
  limit 1;
  </code></pre>
  
  <p>Basically what I'm doing is trying to get the most current data for a device, given that Voltage and Temperature change over time. The DeviceIMEI, ID and ValueDateTime should be unique, but aren't indexed (like I said earlier, I don't administer the database, I only have read permissions).</p>
  
  <p>Edit 2:</p>
  
  <p><strong>Please focus on answering my actual question, not on attempting to rewrite my original query.</strong></p>