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.