My query takes years to count + group by
<p>I have a website with cities and classifieds in cities. Each classified have at least 1 category.</p>

<p>Here is the schema (with only fields we need here) :</p>

<pre><code>Table tv_classified :
id (INT, PK)
village_id (INT) (ID of the classified city)

Table tv_classified_category :
id (INT, PK) (ID of the category)
classified_id (INT, PK) (Foreign key to tv_classified table)
</code></pre>

<p>I'm trying to get the total of classifieds for each categories in each cities. Something quite basic I guess, but sadly <strong>my query takes years</strong> (and I'm not joking, it's still running after 10 minutes with only 100K rows in tv_classified table).</p>

<p>Here is my query, am I missing something ?</p>

<pre><code>SELECT COUNT(*), c.village_id, cat.id as category
FROM tv_classified c
JOIN tv_classified_category cat
GROUP BY c.village_id, cat.id;
</code></pre>

<p>Best regards,</p>