Karolis wrote:
> SELECT COUNT(*) FROM (
> SELECT 1 FROM INDEX_KEYS WHERE KeywordID IN (7, 8) GROUP BY DocumentID HAVING COUNT(DISTINCT KeywordID) = 2) AS x;
> +----------+
> | COUNT(*) |
> +----------+
> | 6204 |
> +----------+
> 1 row in set (0.04 sec)
>
>
> goodness :))))))))
> Can you please explain why this is happening?
Well, you just eliminated a join (the outer SELECT is optimized away).
The subquery finds all documents that are associated with both KeywordIDs 7 and 8.
Since you just want to count the number of rows, you write SELECT 1 ...
By the way, this works with any number of KeywordIDs, as long as you adjust the 2 in the HAVING clause accordingly.
--
felix
Please use
BBCode to format your messages in this forum.