MySQL query to select number of fields occurring more times than x I want to find out the 'rank' of a word in any given language. A word with a rank of 1 means it's the most commonly used word in the language.
There are two MySQL tables.
document:

id: 1
language: &quot;english&quot;

word:

id: 2
body: &quot;the&quot;
document_id: 1

There are about 1 million rows in the word table. Here is a query that works, but it takes 7-10 seconds. Is there a better way to write this query?
select count(*) + 1
    from (
        select word.body, count(*) as count
        from word
        join document on word.document_id = document.id
        where document.language = 'english'
        group by word.body
    ) t
    where count &gt; (select count
    from (
        select word.body, count(*) as count
        from word
        join document on word.document_id = document.id
        where document.language = 'english'
        group by word.body
    ) t
    where body = 'the')
Initially I was thinking of suggesting the HAVING clause, but I don't think that would help in this case, since the query selects the aggregated count and uses that for a comparison.

One option is to use a Common Table Expression (CTE) but apparently those aren't introduced until MySQL version 8. One other option is to make a View:

CREATE VIEW countEnglishWord as select word.body, count(*) as count
    from word
    join document on word.document_id = document.id
    where document.language = 'english'
    group by word.body;


and use it like this:

select count(*) + 1, body
from countEnglishWord c
where count &gt; (select count
  from countEnglishWord t
  where body = 'the'
)


I haven't tested it on a million rows... but 3 in this sqlfiddle.