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).
'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.