Question 336002 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL multiple count(*) after group by

My question is that how many times will COUNT(*) be called for each group? Three times or just one time?
If the answer is three times, how can i optimize it.

SELECT COUNT(*) AS num1, COUNT(*) / 10 AS num2, COUNT(*) * 10 AS num3
FROM table
GROUP BY column_name

This is a mysql sql problem, something to do with COUNT(*) function

Tags:

2

Answers


  1. 0

    In your query, COUNT(*) will be called three times for each group. This can be inefficient because it requires scanning the group multiple times.

    To optimize it, you can calculate COUNT() once and then reuse that result for num2 and num3. You can achieve this by using a subquery or a common table expression (CTE) to calculate COUNT() and then reference it in the outer query. Here’s how you can do it with a subquery:

    SELECT num1, num1 / 10 AS num2, num1 * 10 AS num3
FROM (
    SELECT COUNT(*) AS num1
    FROM table
    GROUP BY column_name
) AS counts;
    Login or Signup to reply.
  2. 0

    An experiment gives strange result.

    SET SESSION cte_max_recursion_depth = 1000000;

CREATE TABLE test 
WITH RECURSIVE cte AS (
  SELECT 1 id UNION ALL SELECT id + 1 FROM cte LIMIT 100000
)
SELECT id, CASE WHEN RAND() > 0.1 THEN 1 END val FROM cte;

SELECT COUNT(*), COUNT(val) FROM test;
    COUNT(*) COUNT(val)
    100000 89870 
    SET @t1 := CURRENT_TIMESTAMP(6);
SELECT COUNT(val) FROM test;
SET @t2 := CURRENT_TIMESTAMP(6);
SELECT COUNT(val), COUNT(val) * 10, COUNT(val) / 10 FROM test;
SET @t3 := CURRENT_TIMESTAMP(6);

    SELECT TIMESTAMPDIFF(MICROSECOND, @t1, @t2), TIMESTAMPDIFF(MICROSECOND, @t2, @t3);
    TIMESTAMPDIFF(MICROSECOND, @t1, @t2) TIMESTAMPDIFF(MICROSECOND, @t2, @t3)
    54495 56852 
    SET @t1 := CURRENT_TIMESTAMP(6);
SELECT COUNT(*) FROM test;
SET @t2 := CURRENT_TIMESTAMP(6);
SELECT COUNT(*), COUNT(*) * 10, COUNT(*) / 10 FROM test;
SET @t3 := CURRENT_TIMESTAMP(6);

    SELECT TIMESTAMPDIFF(MICROSECOND, @t1, @t2), TIMESTAMPDIFF(MICROSECOND, @t2, @t3);
    TIMESTAMPDIFF(MICROSECOND, @t1, @t2) TIMESTAMPDIFF(MICROSECOND, @t2, @t3)
    29794 49933 
    SET @t1 := CURRENT_TIMESTAMP(6);
SELECT COUNT(*) FROM  test WHERE id > 0;
SET @t2 := CURRENT_TIMESTAMP(6);
SELECT COUNT(*), COUNT(*) * 10, COUNT(*) / 10 FROM test WHERE id > 0;
SET @t3 := CURRENT_TIMESTAMP(6);

    SELECT TIMESTAMPDIFF(MICROSECOND, @t1, @t2), TIMESTAMPDIFF(MICROSECOND, @t2, @t3);
    TIMESTAMPDIFF(MICROSECOND, @t1, @t2) TIMESTAMPDIFF(MICROSECOND, @t2, @t3)
    57031 59219

    fiddle

    When we count the number of rows in the table then COUNT(*) is retrieved from the table metadata by the special way, the execution time differs almost twice, i.e. the value of the expression is unlikely to be reused.

    But when we count the amount of non-NULL values or when we use a conditions (the same for counting the amount in the subquery output) then the value is reused – we see approx. the same time for the queries which uses 1 or 3 expression copies.

    Your query uses GROUP BY – so I predict that COUNT(*) will be reused.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search