MariaDB is in "Sending data" state for too long for COUNT

Question

Recently I have encountered an issue of slow data loads.

Upon looking into the Process list I have found out that the issue is on the database side, it gets stuck in the "Sending data" phase for too long - like minutes.

But to my surprise, this wasn't the main data query, this was a COUNT query, so realistically the data being sent is 1 column 1 row with integer ~10000.

The query itself looks something like:

SELECT
    COUNT(*)
FROM
    main_table
    INNER JOIN big_table1 big_table1 ON big_table1.bt1_id = main_table.bt1_id
    INNER JOIN big_table2 big_table2 ON big_table2.bt2_id = main_table.bt2_id
    LEFT JOIN big_table3 big_table3 ON big_table3.tbr_id = main_table.tbr_id
    INNER JOIN big_table4 big_table4 ON big_table4.bt1_id = big_table1.bt1_id
    INNER JOIN big_table5 big_table5 ON big_table5.bt5_id = big_table2.bt5_id
    LEFT JOIN table_b_rel_rel2 table_b_rel_rel2 ON table_b_rel_rel2.rel2_id = big_table3.rel2_id
    LEFT JOIN table_b_rel_rel2_rel3 table_b_rel_rel2_rel3 ON table_b_rel_rel2_rel3.type_id = table_b_rel_rel2.type_id
WHERE
    big_table2.org IN ('1')
ORDER BY
    main_table.some_int_score DESC,
    main_table.primary_key DESC

Now what is very interesting is that this seems to happen only for the initial call with these joins - any other - including different ORDERs on different columns and filtering - seems to go as expected (under 1s).

It 's a MariaDB 10.3 server - what could be the issue? Is it some sort of cache thing? Is there an issue in how the tables are big (big imagine around 1-3M records)? Could there be an issue with the initial ORDER BY being present (it's put-in by a subsystem, not by me directly, not sure if that affects COUNT at all or not)?

What is not clear to me - I would understand if it would get stuck in some processing/executing state, but why does it take so long to "Send data" when the data is just a few bytes? Thank you.

Answer 1

Sending data to the client as part of processing a SELECT statement or other statements that returns data like INSERT ... RETURNING.

"Sending data" is often the longest-occurring state as it also include all reading from tables and disk read (or waiting to read) activities. Where an aggregation or un-indexed filtering occurs there is significantly more rows read than what is sent to the client.

Documentation updated based on this question.

I assume you've cut out result fields in your query as various things will not affect the result and add to the cost because they get executed. I've leaving this for a note for others with slow queries on just COUNT (or other aggregated function):

• LEFT JOIN on a query this doesn't affect the `COUNT(*).
• [INNER] JOIN can be removed if there is always a match.
• Sometimes main_table can be removed if you are counting its PK in a secondary table with filtering.
• ORDER BY on a result of COUNT(*) doesn't change the result.