Hi everyone,
I am trying to retrieve data using below query which is running forever. basically I am doing student level grouping by passing parameters from t4 tables rows.

t1 rows count : 2.5 crores

 CREATE TABLE `t1` (  
  `id` int NOT NULL AUTO_INCREMENT,
  `activity_date` datetime DEFAULT NULL,
  `book_title` varchar(200) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `passed` tinyint(1) NOT NULL,
  `points` int NOT NULL,
  `points_teacher` int DEFAULT NULL,
  `quiz_questions_correct` int DEFAULT NULL,
  `quiz_questions_issued` int DEFAULT NULL,
  `school_year` int DEFAULT NULL,
  `sequence_num` int DEFAULT NULL,
  `title` varchar(200) DEFAULT NULL,
  `type_id` int NOT NULL,
  `book_id` int DEFAULT NULL,
  `grade_id` int DEFAULT NULL,
  `src_quiz_id` int DEFAULT NULL,
  `student_user_id` int NOT NULL,
  `teacher_user_id` int DEFAULT NULL,
  `imported` tinyint(1) NOT NULL DEFAULT '0',
  `status` tinyint(1) DEFAULT NULL,
  `src_activity_type` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK61A6FD28D741EFEC` (`teacher_user_id`),
  KEY `FK61A6FD2863C33D45` (`student_user_id`),
  KEY `FK61A6FD2845A8C289` (`book_id`),
  KEY `FK61A6FD28FD8BD6CB` (`grade_id`),
  KEY `FK61A6FD28F2826674` (`src_quiz_id`),
  KEY `ix_src_activity_activity_date` (`activity_date`),
  KEY `ix_src_activities_year_date_questions_correct` (`school_year`,`activity_date`,`quiz_questions_correct`),
  KEY `ix_status` (`status`),
  KEY `ix_quiz_questions_issued` (`quiz_questions_issued`),
  KEY `ix_type_id` (`type_id`),
  KEY `ix_src_created` (`created`),
  CONSTRAINT `FK61A6FD2845A8C289` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK61A6FD2863C33D45` FOREIGN KEY (`student_user_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK61A6FD28D741EFEC` FOREIGN KEY (`teacher_user_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK61A6FD28F2826674` FOREIGN KEY (`src_quiz_id`) REFERENCES `src_quizzes` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK61A6FD28FD8BD6CB` FOREIGN KEY (`grade_id`) REFERENCES `grades` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=23841372 DEFAULT CHARSET=latin1

t2 rows count : 2.7 crores

CREATE TABLE `t2` (
  `src_activity_id` int NOT NULL,
  `lf_class_id` int NOT NULL,
  KEY `FK2159E1D8D21506D6` (`lf_class_id`),
  KEY `FK2159E1D8205CF734` (`src_activity_id`),
  KEY `idx_ClassId_Activity_ID` (`lf_class_id`,`src_activity_id`),
  KEY `idx_Activity_ID` (`src_activity_id`),
  CONSTRAINT `FK2159E1D8205CF734` FOREIGN KEY (`src_activity_id`) REFERENCES `src_activities` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK2159E1D8D21506D6` FOREIGN KEY (`lf_class_id`) REFERENCES `lf_classes` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

**t3 rows count : 10k**  
CREATE TABLE `t3` (
  `id` int NOT NULL AUTO_INCREMENT,
  `avg_quiz_lexile` int DEFAULT NULL,
  `avg_quiz_score` int DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `lexile` int DEFAULT NULL,
  `lexile_fully_computed` tinyint(1) NOT NULL,
  `lexile_updated` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `num_quiz_attempted` int DEFAULT NULL,
  `num_quiz_passed` int DEFAULT NULL,
  `points_earned` int DEFAULT NULL,
  `slz_id` varchar(200) DEFAULT NULL,
  `timezone_offset` int DEFAULT NULL,
  `words_read` int DEFAULT NULL,
  `school_id` int DEFAULT NULL,
  `school_group_id` int DEFAULT NULL,
  `active` tinyint(1) DEFAULT '1',
  `on_board_status` int DEFAULT NULL,
  `grade_code` varchar(255) DEFAULT NULL,
  `grade_name` varchar(255) DEFAULT NULL,
  `first_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `last_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slz_id` (`slz_id`),
  UNIQUE KEY `users_unique_slz_id` (`slz_id`),
  KEY `FK6A68E08AB2ED262` (`school_group_id`),
  KEY `FK6A68E082A4052E9` (`school_id`),
  KEY `ix_users_active` (`active`),
  CONSTRAINT `FK6A68E082A4052E9` FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK6A68E08AB2ED262` FOREIGN KEY (`school_group_id`) REFERENCES `school_groups` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=852699 DEFAULT CHARSET=latin1

t4 rows count : can be 100k but passing only 3000 records in cte

CREATE TABLE `t4` (
  `id` int NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `school_id` int NOT NULL,
  `grade_id` int NOT NULL,
  `class_id` int NOT NULL,
  `student_id` int DEFAULT NULL,
  `activity_type` int NOT NULL,
  `school_year` int NOT NULL,
  `activity_date` datetime NOT NULL,
  `batch_time` datetime NOT NULL,
  `status` int NOT NULL,
  `batch_count` int NOT NULL,
  `version` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_batch_class_report_etl` (`school_id`,`grade_id`,`activity_type`,`batch_time`,`activity_date`,`class_id`,`student_id`),
  KEY `FK_GRADES_JOIN_WITH_CLS` (`grade_id`),
  KEY `FK_CLASS_JOIN_WITH_CLS` (`class_id`),
  KEY `ix_class_etl_created` (`created`),
  KEY `ix_class_etl_activity_type` (`activity_type`),
  KEY `ix_class_etl_activity_date` (`activity_date`),
  KEY `ix_class_etl_batch_time` (`batch_time`),
  KEY `ix_class_etl_status` (`status`),
  KEY `idx_batch_class_etl_jobs_School_Year` (`school_year`),
  KEY `idx_Covering` (`status`,`activity_type`,`class_id`,`school_year`,`activity_date`),
  CONSTRAINT `FK_CLASS_JOIN_WITH_CLS` FOREIGN KEY (`class_id`) REFERENCES `lf_classes` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_GRADES_JOIN_WITH_CLS` FOREIGN KEY (`grade_id`) REFERENCES `grades` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_SCHOOLS_JOIN_WITH_REP_CLS` FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=9032408 DEFAULT CHARSET=utf8mb3  

Below is the query I am using to retrieve data

with cte as (
select distinct class_id,school_year,activity_type,DATE_ADD(Date(activity_date), INTERVAL 1 DAY) activity_date from t4 where status=2 and activity_type=1  and activity_date<='2022-06-30 00:00:00'
)
 SELECT -- count(*)
 et.activity_date,et.class_id,et.school_year,   u.id AS studentId,
 SUM( sa.quiz_Questions_Correct) AS quizQuestionsCorrect
 ,SUM( sa.quiz_Questions_Issued) AS quizQuestionsIssued
FROM t1 sa
JOIN t2 salc ON sa.id=salc.src_activity_id  and sa.status<>1 AND sa.type_Id = 1 AND sa.quiz_Questions_Issued IS NOT NULL
JOIN t3 u on u.id=sa.student_User_ID
JOIN cte et on  et.school_year=sa.school_year  AND  sa.activity_Date <=et.activity_date AND et.class_id=salc.lf_class_id 
WHERE  
  sa.status<>1
 AND sa.type_Id = 1
 AND sa.quiz_Questions_Issued IS NOT NULL
 AND sa.activity_Date <=et.activity_date
AND sa.id = (SELECT MAX(sa_max.id) FROM t1 sa_max
        JOIN t2 salc_max ON sa_max.id=salc_max.src_activity_id
            WHERE sa_max.student_User_ID = sa.student_User_ID  AND sa_max.school_year =sa.school_year
             AND sa_max.src_Quiz_ID = sa.src_Quiz_ID AND sa_max.quiz_Questions_Issued IS NOT NULL AND sa_max.type_Id = sa.type_Id
           -- ORDER BY sa_max.activityDate DESC, sa_max.modified DESC
         ) 
 GROUP BY et.activity_date, u.id,et.class_id,et.school_year  

How can I optimize it as Indexes are already include in create tables script. even I tried same query for one line item from t4 but it takes 10 seconds for this too.

select u.id, SUM(sa.quiz_Questions_Correct) quizQuestionsCorrect,SUM(sa.quiz_Questions_Issued) quizQuestionsIssued
 from t1 sa
JOIN t2 salc ON sa.id=salc.src_activity_id 
JOIN t3 u on u.id=sa.student_User_ID
where lf_class_id = 33226 AND sa.status <> 1 
AND sa.type_Id = 1 -- + SrcActivity.ActivityType.QUIZ.getValue()
AND sa.activity_Date <= '2022-08-05'-- DATE_ADD('2022-08-04', INTERVAL 1 DAY) 
 AND sa.school_year = 2021
 AND sa.id = (SELECT MAX(sa_max.id) FROM t1 sa_max
     JOIN t2 salc_max ON sa_max.id=salc_max.src_activity_id WHERE 
sa_max.student_User_ID = sa.student_User_ID AND salc_max.src_activity_id =sa.id  
             AND sa_max.school_year = sa.school_year AND sa_max.src_Quiz_ID = sa.src_Quiz_ID AND sa_max.quiz_Questions_Issued IS   NOT NULL   AND sa_max.type_Id =sa.type_Id           
        ) 
AND sa.quiz_Questions_Issued IS NOT NULL group by u.id