New answers tagged primary-key
I have to presume that the application is written in such a way that it tries the insert, and if it fails, it just tries again. Otherwise the insert will fail (PK violation) and your users will have complained and filed bugs. If the purpose of this is to just assign unique numbers in a random order, then it may be the case that the application is working ...
Just found out very neat explanation: superkey: a set of attributes which will uniquely identify each tuple in a relation candidate key: a minimal superkey primary key: a chosen candidate key secondary key: all the rest of candiate keys prime attribute: an attribute that is a part of a candidate key (key column) nonprime attribute: a nonkey column
It depends on the purpose of the key. "Surrogate key" can mean different things to different people; to my mind, it means a candidate key (a field or combination of fields which uniquely identify a record) other than the primary key. For example, social security number may be a surrogate key for customers (not a very good one, mind). If the values are ...
A table can and should have as many keys as it needs. Usually when a surrogate key is used it means you will also want some alternative key as well (variously called a domain key, natural key or business key). The practice of designating any one key as "primary" is of no great significance. It is simply a convention to mark one of the keys as "preferred" or ...
I know this is a bit late to the game, but might I add... We have a similar situation except that where ID1 and ID2 are the same, they actually link to the same table providing a related feature of sorts, again, many to many. Since we'd be querying looking for potential ID's that might exist in either column, two indexes is actually more logical.... and ...
I think that better indexes for these queries are these (which you don't have on the tables now): (player_id, day_id) and (target_id, day_id) The optimizer choosing different indexes in the two situations has probably to due with table sizes and selectivity of the available indexes. I suggest you add the two indexes above and check (and compare) the new ...
You can use the index hints: USE, IGNORE and FORCE. Example: SELECT * FROM table1 USE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3; Explanation from the manual: By specifying USE INDEX (index_list), you can tell MySQL to use only one of the named indexes to find rows in the table. The alternative syntax IGNORE INDEX (index_list) ...
If the third table is related to employees and not to every user, you should reference employees.userid. It makes sense for, say, a table of employee identity card numbers to reference "employees"."userid". But it makes sense for a table of email addresses to reference "users"."userid", because every user has an email address.
Only rows in 7 partitions qualify the predicate(s) so there is no need to ever lookup rows in the other 3 partitions.
Top 50 recent answers are included