0

I have a very large MySQL8 database, currently using the InnoDB engine, that I have to perform some COUNT() queries on. Specifically, something like this:

SELECT parameter, COUNT(*) FROM table WHERE ... GROUP BY parameter

Count operations are relatively slow, since they have to scan the full table (or in this case, the full results of the WHERE condition), so I'm wondering if I can put a limit on each result as a way to speed up the query.

For example, consider a table foo with a single indexed column country. If I want to query the distribution of countries in this table, right now I run something like this:

mysql> SELECT country, count(*) FROM foo GROUP BY country ORDER BY count(*) DESC;
+---------+----------+
| country | count(*) |
+---------+----------+
| US      |   401238 |
| CA      |    14316 |
| HN      |    10636 |
| MX      |     5534 |
...

... in my test table, which has ~500k entries, this takes ~3s to complete. However, if I only want the countries without the count, that query only takes a few milliseconds. This is not surprising because in that second scenario there is no COUNT() and hence mysql doesn't have to search the whole table. So to my question, is there any way I could say "if the count is higher than, say, 10k - stop counting for this country". I would expect a result like the following:

+---------+----------+
| country | count(*) |
+---------+----------+
| US      |    10000 |
| CA      |    10000 |
| HN      |    10000 |
| MX      |     5534 |
...

...and ideally a notable speed up, assuming MySQL could scan the table and once it hits 10k records for US, leverage the index to skip all 390k additional US entries and continue counting other countries?

I could also achieve this result by making many independent queries. E.g. I could run SELECT country FROM foo GROUP BY country and a subsequent SELECT * FROM foo WHERE country = bar LIMIT 10000 for each country bar returned by the first query, but since my actual database is only counting a subset using a WHERE condition, that WHERE condition would be run for every query which would add a lot of overhead.

2
  • Test SELECT country, count(country) AS cnt FROM foo GROUP BY country ORDER BY cnt DESC;. But anycase you will have filesort. is there any way I could say "if the count is higher than, say, 10k - stop counting for this country" No. – Akina Mar 4 at 5:01
  • @Akina that is the same as I'm using now only count(*) > count(country) no? That makes the query slightly slower as MySQL then has to confirm if country is non-null where as count(*) will count any row (null or non-null) – Eric Mar 4 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.