I have a table which has the following columns:
CREATE TABLE example ( exid INT, extype INT, studentid INT, (more columns) )
The table will have hundreds of thousands rows, and each record will have distinct value of exid, extype and studentid. While studentid and exid will have plenty of distinct values, extype will have max. 6 values. The table will be joined in the following way:
SELECT ... FROM example ex LEFT JOIN otherTable1 ot1 ON ex.studentid = ot1.studentid AND ex.exid = ot1.exid AND ex.extype = 1 LEFT JOIN otherTable2 ot2 ON ex.studentid = ot2.studentid AND ex.exid = ot2.exid AND ex.extype = 2
Can you suggest me what index(es) on example table will be the most suitable in regards of performance here (I should also add other tables used for joins will go into millions of rows)?