I have a situation where I need to show a list of subjects to which a learning material or a test has been published to. The query below works but it takes about ~8s. Is there a better way I can optimize this query? Thank you.
SELECT sj.*, sj.id AS id FROM subjects sj WHERE ( (SELECT COUNT(lmc.id) FROM learning_materials_codes lmc INNER JOIN students s ON lmc.student_id = s.id INNER JOIN learning_materials lm ON lmc.learning_material_id = lm.id WHERE lmc.student_id = 1 AND sj.id = ANY(lm.subject_ids)) > 0 OR (SELECT COUNT(tc.id) FROM test_codes tc INNER JOIN students s ON tc.student_id = s.id INNER JOIN tests t ON tc.test_id = t.id WHERE tc.student_id = 1 AND t.subject_id = sj.id) > 0 ) AND sj.school_id = 1
Table structure below subjects ~~~~~~~~~~~ id name school_id ... students ~~~~~~~~~~ id f_name l_name school_id ... tests ~~~~~~~~~ id title subject_id ... test_codes ~~~~~~~~~~ test_id student_id code ... learning_materials ~~~~~~~~~~~~~~~~~ id title subject_ids  ... learning_material_codes ~~~~~~~~~~~~~~~~~~~~~~~~ learning_material_id student_id code ...
Note: Each time a learning material or a test is published an access code is generated for students and that data is kept in