Mysql index and key

Question

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 :

What is key(studentId,courseId) and what are the parameters. Also how to do this in PhpMyAdmin.
What is index button in PhpMyAdmin ( query code is ADD INDEX ).
If I want to make a column to be foreign key which may have redundant value in the child table, what do I do? For now I indexed that column instead of making it unique. Is it correct or there exists any better way to do that?

it's not about phpmyadmin it's about SQL. You need to read about basics. — Robert, Sep 18 '15 at 13:11
you marketing my Answer over there? awesome ! For #3, the `SCJunction` table handles that for you, as seen in a course being taken by many students, and a student taking many courses — Drew, Sep 18 '15 at 13:11
@Robert I added that tag because a part of my question is related to that. Should i remove that tag ? — JWonderchild, Sep 18 '15 at 13:13
@Drew i got the 3rd one. Thank you. If you don't mind would you please explain me 1 , 2 ? — JWonderchild, Sep 18 '15 at 13:15
A key in an index. There are references over there about keys. That key is a composite index, meaning it is a combination of two or more columns. Check out the stuff and the links. That Answer hopefully slowly walks ones mind through it top to bottom (with studentId 1,2,3 etc) — Drew, Sep 18 '15 at 13:17
@Drew what's the point of doing it twice as in your example with arguments exchanged ? ' key (studentId,courseId), key (courseId,studentId), ' — JWonderchild, Sep 18 '15 at 13:24
I answered it in the Answer below, but I will answer it in detail at the bottom of my Answer in 5 to 10 minutes with a sample of at least 2 queries — Drew, Sep 18 '15 at 13:27

score 0 · Answer 1 · answered Sep 18 '15 at 13:14

0

1) A "key" applies to it being "the most important thing", and is always unique. The "Primary" is usually used as a unique identifier for this reason.

2) The index applies to how quickly the records can be retrieved later. Like have a list at the back of a book.

3) A foreign key is simply a reference to a primary key that is in a different table. Makes it easier to figure out how to join things.

answered Sep 18 '15 at 13:14

durbnpoisn

4,666
2
16
30

ad1. what about many to many :) Key not always is unique – Robert Sep 18 '15 at 13:17
I suppose this is true. I've seen cases where tables have 3 keys. Which combined make for a unique ID. So, number one is not exactly correct. – durbnpoisn Sep 18 '15 at 13:21

Drew · Accepted Answer · 2015-09-18T14:28:06.877

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:

Just 2

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.

Thanks alot , now it makes sense to me ! – JWonderchild Sep 19 '15 at 07:40 — JWonderchild, Sep 19 '15 at 07:40

score 0 · Answer 3 · edited Jan 14 '17 at 11:19

You need to learn about basics of SQL.

Key, there are plenty of key types, there is primary key which uniquely identifies each record in a database table note that key may exists on several fields. There are also foreign keys which defines relation to the other table.

Indexes are special database structure that speeds up data retrieval. There are plenty of index type. You can read more about it here

3 question was explained by @durbnpoisn

Mysql index and key

3 Answers3

Just 2