title: Faster alternatives for "IN" statements?
body: <p>I'm not great with MySQL, so I often find myself preparing sub-optimal queries that work, but I know must be horribly inefficient. I'm hoping you guys could give me some pointers on why the following query doesn't work well, and what methods I should use to accomplish similar queries.</p>
  
  <p>I have the following table structure:</p>
  
  <pre><code>TABLE Files
  files_id =&gt; INT(12), PRIMARY, AUTO INCREMENT, NOT NULL
  files_name =&gt; VARCHAR(255), NOT NULL
  (some other fields such as file type etc)
  
  TABLE File_Permissions
  perm_id =&gt; INT(12), PRIMARY, AUTO INCREMENT, NOT NULL
  perm_files_id =&gt; INT(12), NOT NULL
  perm_users_id =&gt; INT(12), NOT NULL
  </code></pre>
  
  <p>I pull a list of the files a user is allowed to view with the following SQL:</p>
  
  <pre><code>SELECT files_name FROM Files WHERE files_id IN 
      (SELECT perm_files_id FROM File_Permissions WHERE perm_users_id = 'xxxxxx');
  </code></pre>
  
  <p>This, as far as I can tell, will go through each of the thousands of records in the Files table, and for each one execute a subquery that selects from the File_Permissions table to check against the user's ID. </p>
  
  <p><strong>This takes almost 2 seconds per query.</strong> I'm sure something is fundamentally wrong with this, I just don't know what it is. </p>
  
  <p>Thanks so much for the help!</p>