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)?
Index(studentid,exid,extype)
on example andindex(studentid,exid)
on otherTable1 might help. – Mihai Feb 13 '14 at 13:24