Count, union all queries are running for very long time.

Query: select COUNT(1) from t1 INNER JOIN t2 use INDEX(idx1) on t2.c1= 'BN' and t2.c2= t1.c1 and t2.c3= t1.c2 where t1.c3 BETWEEN date_format(date_sub(current_date(), interval 1 year), '%Y%0101') and date_format(last_day(current_date()), '%Y0228') and t1.c4= 'IC'; Previously(2 months ago) this query was taking only 1 minute to execute. but when I tried again after 2 months this query was never completing. I waited for more than 10 minutes but it was not complete. Without count in select, this query gives results immediately. Only changes happened during this time was: - data increased from 10 million to 10.5 million in the specific table - overall DB server occupied space increased from 70%(3TB) to 90%(3.8TB) due to data migratons - Indexes increased from 5 to 10 on almost 40% of the tables(But without count my query is giving results immediately)

I am facing this issue with all the count, union all throughout all the queries I am running or I have used in procedures. I would like to mention that: - DB is up since last 3 months. - I have changed collation and charset parameters from utf8 to utf8mb4. - And i found one strange entry in output of 'select * from information_schema.INNODB_TRX;'. with thread id = 0 "trx_id" : 10687032807, "trx_state" : "RUNNING", "trx_started" : "2023-07-07T08:48:10.000Z", "trx_requested_lock_id" : null, "trx_wait_started" : null, "trx_weight" : 437630474, "trx_mysql_thread_id" : 0, "trx_query" : null, "trx_operation_state" : "", "trx_tables_in_use" : 0, "trx_tables_locked" : 0, "trx_lock_structs" : 0, "trx_lock_memory_bytes" : 1128, "trx_rows_locked" : 0, "trx_rows_modified" : 437630474, "trx_concurrency_tickets" : 0, "trx_isolation_level" : "REPEATABLE READ", "trx_unique_checks" : 1, "trx_foreign_key_checks" : 1, "trx_last_foreign_key_error" : null, "trx_is_read_only" : 0, "trx_autocommit_non_locking" : 0.

And procedure started taking a lot of time like 3-4 minutes for 1 record. Previously within seconds they were processing at least more than 2000 records: My procedures were like: Proc_name--> Cursor--> (select query)--> Variables--> Begin--> loop--> fetch_cursor--> select query check--> insert or update--> commit--> end loop--> end; I enveloped all select,update,insert procedure query into execute immediate statement and after that procedures started working fast.