0

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.

0

1 Answer 1

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.
2
  • Dan, It would be better to change the wording of "Sending data" (and some of the other states). Anyway, I am glad the doc is updated.
    – Rick James
    Sep 14, 2023 at 3:49
  • I haven't found an existing MDEV for such a request though it seems to be a common enough frustration. Suggestions on text welcome.
    – danblack
    Sep 14, 2023 at 4:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.