Counting Words with Occurrences Greater Than 2 in MySQL: Optimized Application of GROUP BY and HAVING

Problem Background and Common Errors

In database applications, counting the frequency of repeated words in text is a common requirement. Assume a table named words with the following structure:

+  id  +  word  +
+------+--------+
+   1  + hello  +
+------+--------+
+   2  + bye    +
+------+--------+
+   3  + hello  +
+------+--------+

This table stores lowercase forms of words from text, e.g., the text "Hello bye hello" is parsed into three records. The goal is to count the number of words that appear at least twice; in the example, the expected result is 1 (only "hello" is repeated).

Beginners might attempt an erroneous query like:

SELECT COUNT(id) FROM words WHERE (SELECT COUNT(words.word))>1

This query has multiple issues: first, the subquery (SELECT COUNT(words.word)) lacks a GROUP BY clause, returning the total row count instead of grouping by word; second, the condition in the WHERE clause cannot correctly filter repeated words; most critically, this approach causes severe performance degradation with large datasets, as it may execute unnecessary subqueries per row, leading to an "N+1" query problem.

Core Solution: GROUP BY and HAVING

The correct solution leverages a combination of GROUP BY and HAVING clauses. GROUP BY groups records by word, while HAVING filters the grouped results. Here is the step-by-step implementation:

First, retrieve all repeated words and their occurrence counts:

SELECT word, COUNT(*) AS cnt
FROM words
GROUP BY word
HAVING cnt > 1

This query works as follows: GROUP BY word aggregates records with the same word into groups, COUNT(*) calculates the number of rows per group (i.e., word occurrences), and HAVING cnt > 1 filters groups with counts greater than 1. For the example data, the result will be:

+  word  +  cnt  +
+--------+-------+
+ hello  +   2   +
+--------+-------+

To count the number of these repeated words, use the above query as a subquery with an outer COUNT(*):

SELECT COUNT(*)
FROM
(
    SELECT NULL
    FROM words
    GROUP BY word
    HAVING COUNT(*) > 1
) T1

Here, the subquery returns groups for all repeated words (using SELECT NULL to minimize data return, as only the row count matters), and the outer query counts the rows. In the example, this returns 1.

Performance Analysis and Optimization

Compared to the erroneous query, this solution offers significant performance advantages:

• Reduced Query Count: The erroneous query might execute subqueries per row, leading to O(n²) complexity; the optimized solution completes with a single GROUP BY scan, achieving O(n log n) complexity (assuming index usage).
• Index Utilization: If the word column is indexed, GROUP BY can efficiently use it for grouping.
• Memory Efficiency: HAVING filters after grouping, avoiding unnecessary storage of intermediate results.

For very large datasets (e.g., over a million rows), consider these optimizations:

1. Ensure the word column is indexed to speed up grouping: CREATE INDEX idx_word ON words(word);
2. If only the count is needed, not the specific words, use SELECT NULL to reduce data transfer.
3. Monitor query execution plans using EXPLAIN for performance analysis.

Extended Applications and Considerations

This method can be extended to similar scenarios, such as:

• Counting words with occurrences greater than any threshold k: replace k in HAVING cnt > k with the desired value.
• Retrieving detailed information on repeated words: use the inner query directly without outer counting.
• Combining with other aggregate functions: e.g., calculating the average occurrence count of repeated words.

Important considerations:

• The HAVING clause must reference aggregate functions (e.g., COUNT(*)) or aliases (e.g., cnt), not column names directly.
• In MySQL, GROUP BY implicitly sorts, but this should not be relied upon; add ORDER BY explicitly if specific ordering is needed.
• For case-sensitive scenarios, ensure data consistency (e.g., storing in lowercase as in the example).

By correctly applying GROUP BY and HAVING, developers can efficiently address word repetition statistics, enhancing database query performance.