comp_entrants should have at least one primary key and two foreign keys.
Primary key
-- Either
PRIMARY KEY(comp_id, username)
-- or
PRIMARY KEY(username, comp_id)
The primary key is an index with uniqueness constraint. In this case, the composition of comp_id and username has to be unique which means that there can be only one entry for comp_id = 1 and username = 'joe', but there can be another entry for comp_id = 1 and username = 'bob' or comp_id = 2 and username = 'joe'.
The difference in the two versions above is that a composite index can be used only as long as a search includes the first N index fields, which means that the order of the fields changes when the index can be used if only one field is present in a where or join clause.
-- The first primary key version can be used here, but not the second, since the only
-- index field present is comp_id
SELECT ...
FROM comp_entrants
WHERE comp_id = 1
-- Here the second version of the primary key can be used, but not the first
-- since there is a join on ce.username, but not comp_id
SELECT ...
FROM comp_entrants ce
INNER JOIN user u ON u.username = ce.username
WHERE u.somefield = 'somevalue'
For this reason you might want to have both
PRIMARY KEY (comp_id, username) and INDEX (username) or
PRIMARY KEY (username, comp_id) and INDEX(comp_id)
I'd suspect that it's better to only have username indexed once, since that's a string index, but I don't really know and my gut feeling has been wrong before. It probably won't matter much anyway, but if you want to find out, try googling for "composite index" or something like that.
The foreign keys
FOREIGN KEY (comp_id) REFERENCES competitions(id)
FOREIGN KEY (username) REFERENCES users(username) ON DELETE CASCADE
Wether you end none, one or both with "ON DELETE CASCADE" depends on what kind of behaviour you want when trying to delete an entry in table users/competitions. Without cascading deletes, you can't delete a user and/or competition that is referenced in comp_entrants table. With cascading delete, all referencing entries in comp_entrants would be automatically deleted when you delete a referenced user and/or competition.
And just like primary keys are indexed, so are foreign keys, so these 3 to 4 indices are all you need. Sometimes everything in a table will be indexed, sometimes nothing more than the primary key. There are a few typical cases deciding on wether to add an index or not.
Add index when
- field(s) is primary key (that is, you define the field(s) as primary key)
- field(s) is foreign key (that is, you define the field(s) as foreign key)
- you will search / join using this field "frequently". For example, if you want to let users search for other users based on their birthdate, you should index this field.
When it comes to "frequently" above, I'd say that if any user can search on that field, index it. If no user other than yourself or other developers will search/join on a particular field and you believe this to be infrequent, an index may not be needed. Should you one day feel that it is needed, then you can also easily add it.
Do not add index when
- you will "never" search / join on a field. If "never" changes in the future, you can add an index later.
- you very rarely SELECT data from the table, but you write a lot to it. Creating index entries take time, so on each insert / update, the indices have to be updated. The time needed to do so should not be significant in comparison to time saved on once per year SELECTS. And with frequent enough inserts / updates, your database might not even cope with too many indices.
Finally, you have to understand that the use of indexing will not really make any difference before you have a lot of data in your tables, say from tens to hundreds of thousands of rows. But once you get there, lacking a single index may make your service unavailable since each user is suddenly waiting 30 seconds on a single query.
