Speed up a slow SQL query on a large database
<h1>This is a picture-type webpage</h1>

<p>The code should select the next tag with the least amount of unrated images.</p>

<h2>Table info</h2>

<h3>table_name = images</h3>

<ul>
<li>filename (varchar255)</li>
<li>pick (tinyint)</li>
<li>reject (tinyint)</li>
</ul>

<h3>table_name = image_tags</h3>

<ul>
<li>filename (varchar255)</li>
<li>tag_name (varchar255)</li>
</ul>

<h3>Indexes</h3>

<p>The flowing columns has indexes:</p>

<ul>
<li>images.filename</li>
<li>images.pick</li>
<li>images.reject</li>
<li>image_tags.filename</li>
</ul>

<h2>Functionality</h2>

<p>The code should go through every row and check how many duplicate tag_name-column there are. Afterwards it should return the tag with the least number of duplicate rows. The code should ignore any rows where <code>pick = TRUE</code> or <code>reject = TRUE</code></p>

<p>Meaning the code should only include files if <code>pick = FALSE</code> and <code>reject = FALSE</code></p>

<h2>Code</h2>

<p>At the moment my code is the folowing.</p>

<pre><code>SELECT image_tags.tag_name, COUNT(*) as number_of_rows FROM image_tags JOIN images ON image_tags.filename = images.filename WHERE images.pick = FALSE AND images.reject = FALSE GROUP BY image_tags.tag_name ORDER BY number_of_rows
</code></pre>

<h2>Question</h2>

<p>Because of the size of the database the query takes a lot of time (sometimes as much as 30 seconds). Is there a way to make the query run faster, or is there another query that would have less execution time?</p>

<h3>Size</h3>

<ul>
<li>images-table: 700740 rows</li>
<li>image_tags-table: 823852 rows</li>
</ul>