I'm pretty new to MySQL and database. Yesterday I came across this How to store multiple options in a single table?. I've few questions related to that which are as follows :
That answer of mine in that link shows the following three keys (which are indexes). KEY and INDEX are synonyms.
There are manual page links over there and other links. As for the keys in SCJunction table (ignoring the primary key), they are
unique key(studentId,courseId,term), -- no duplicates allowed for the combo (note student can re-take it next term) key (studentId,courseId), key (courseId,studentId),
So here is the importance of them. First, all 3 of those just shown are composite indexes. The orders are flipped for #2 and #3, so that depending on the universe of queries that go after your data, the db engine can choose which index to use for fastest retrieval based on the left-most column you are going after.
I will expand on the concept of left-most below shortly.
As for the unique key, #1, the comment next to it stating enforcing no duplicates (meaning junk data) is rather self-explanatory. For instance, student 1 course 1 term 1 cannot exist twice in that table.
It is true that those indexes could be simplied for all users and their throughput by reducing them to the two following ones:
unique key(studentId,courseId,term), -- no duplicates allowed for the combo (note student can re-take it next term) key (courseId,studentId),
The three were for illustration purposes, and as I was trying to add depth to the Answer over there, you exposed the fact that an extra KEY (INDEX) was not necessary. As the UNIQUE key (going the other way) would satisfy the conditions that the three would. Hopefully the examples about to be shown below can add some clarity to that.
Two example queries, that illustrate the composite indexes, and why have two of them
How many courses is Kim taking this term (term=100)
select count(*) as courseCount from SCJunction where studentId=2 and term=100 +-------------+ | courseCount | +-------------+ | 3 | +-------------+
That answer may be 2, because we delete a course for Kim in that example (in the other question/answer link)
How many students are taking that Chaucer class (courseId= 5, term=100)?
select count(*) as studentCount from SCJunction where courseId=5 and term=100 +--------------+ | studentCount | +--------------+ | 2 | +--------------+
The two queries above will benefit from indexes, but not the same index. This is because the where clause is searching based on different criteria. Criteria of a column that is present left-most in two different indexes. Left-most means, for composite indexes, starting from the left and going right (just the way you would read it in English), is this an index that can benefit my search. If it is not, then it is not used. If none are used, you get the dreaded tablescan of all data without the use of indexing.
The use of a composite index may be partial, such that only up to a certain count of columns (from left-most onward) are of use to the db engine. But at least the index is used to get to that subset of data.